Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash desktop dns driver drivers error ethernet excel freeze gaming graphics hard drive hardware hdmi internet laptop malware memory monitor motherboard network printer problem ram registry repair router slow software sound trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Creating Macro/script in Excel

Reply  
Thread Tools
Couriant's Avatar
Distinguished Member with 27,145 posts.
 
Join Date: Mar 2002
Location: Chillin in AZ
Experience: B.S. in M.I.S
23-May-2008, 09:49 PM #1
Creating Macro/script in Excel
Can someone please help me build a script in Excel. What I need to do is from a spreadsheet that is already created either pull data from it and create a separate sheet, or delete certain rows/columns. If the latter, (which may be the better option here), I need columns B, C, and all of the other columns that have 16000000 in them to be deleted.

I have attached a sample file for your reference.

15 years of MSO experience and not once made a script/macro

Attached Files
File Type: xls tsg.xls (24.5 KB, 695 views)
__________________
Marlene Porter aka. Angelize56 - July 21st 1956 to July 14th 2007 -- Rest in peace Angelcakes :*(
I just had a newborn... so please wait while I try to get a chance to get online.
DO NOT send me email, or request MSN Messenger help, they will be canceled and blocked.
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
24-May-2008, 09:30 AM #2
All I can say is that you have been using up a lot of time that could be saved using macros, so now is a good time to start.
So that we can be clear on what you want.
You want to delete columns B & C.
Then you want to delete any column that has 160000000 or 15999996 in them, is the 16000000 always in the first "data" row?
__________________
OBP
I do not give up easily
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
24-May-2008, 05:09 PM #3
Hello there,

I guess the question is: Is this your original data? Is this a smaller subset of your original data? What cells/rows/columns of your sample file do you want to get rid of? If this is your original data, I generally find it better to manipulate a copy of your data, so you retain a small bit of redundancy - which is generally a good thing when working with computers and files when you don't want to lose data or its integrity.

What we would like to know is the entire scope of your application here. Is this done with multiple files? Will this be done only once or used again and again? If multiple times, only on one computer? So, would this be better as an add-in or macros in a workbook. Also, if this is something you want done repeatedly, would this go better with a custom menu or toolbar? I'm assuming this is Excel 2003 or prior, but if migrating to 2007, would you want a customized Ribbon, and if so what would be the scope of it (i.e. for one workbook only, or available to all workbooks)?

Deleting/inserting is generally not efficient. What is better (generally) is to use the native features of Excel and massage the data the way you want so that is all that shows. Then you can move the existing data to anywhere you like, i.e. a new workbook. The other thing to think about is a pivot table, where you can show data in multiple ways. In the end, we really need to know more about what you're doing to give you an educated guess on the best solution for your needs.

HTH
johncaulfield's Avatar
Junior Member with 4 posts.
 
Join Date: May 2008
Location: LOndon
Experience: Intermediate
24-May-2008, 05:47 PM #4
I have test the following loop which repeats deleting the column while the condition is true

Sub test()
While Cells.Find(What:="16000000", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Wend

End Sub


It errors at the end when all the columns containing the value are deleted. It will take a better program to neaten it up
Couriant's Avatar
Distinguished Member with 27,145 posts.
 
Join Date: Mar 2002
Location: Chillin in AZ
Experience: B.S. in M.I.S
24-May-2008, 09:44 PM #5
OK, here is the 411 (which I should have mentioned in the first post lol )

I work for a service company selling electronic items. My department is servicing photocopiers, mainly Xerox ones. I have a client that has been complaining about a lot of things, one of which was this report that comes from a Xerox copier. Originally it was a complete mess because of a configuration error from the original technician. I have made some changes to the copier that now generates a more neater report (the one you see now). But that's STILL not enough...

What the report shows is usage data of each user (or user code entered). This includes copies, prints, or even scan for emailing. Unfortunately the report also shows the limits of each user [code] and the remaining counts. By default this particular Xerox machine limit is 16 million. (why? who knows)

The report is actually created as a comma delimited file (.csv), not XLS. The file I attached on the first post is the original .csv file that was made from the machine. I changed it to .xls because this site does not support .csv attachments.

My client is only interested in the counts only. The limits and counts remaining is not required (actually it's redundant) as well as a few others. I think I can actually take care of the rows, as those are user and group accounts. I think I will end up putting all the user codes in one group since each department has its own code. So really it's the columns that need to be tidied up. The columns are fixed and never changes so the script/macro (if possible) will not need to be changed once made.

So to recap, all I really need to do is to remove columns. I do not need columns B, C, S-V, and columns with Limit or Remaining in it (see row 1). In fact we may not need columns after M, but I will need to check that.

Does this help?

EDIT: In fact the original file before I 'fixed' it is on this thread, which OBP has posted too.
__________________
Marlene Porter aka. Angelize56 - July 21st 1956 to July 14th 2007 -- Rest in peace Angelcakes :*(
I just had a newborn... so please wait while I try to get a chance to get online.
DO NOT send me email, or request MSN Messenger help, they will be canceled and blocked.

Last edited by Couriant; 24-May-2008 at 09:46 PM.. Reason: extra info
The Villan's Avatar
Senior Member with 2,003 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
Experience: Advanced at times
25-May-2008, 04:19 AM #6
If you have a CSV file where all the data is seperated by commas, then you should be able to open it into excel.
All the data will be in column A
You can use the Data Text to Columns to seperate the data into seperate columns and exclude the columns you don't want.
Below is a simple example of how to do this and how to exclude columns that you don't need
I hope I have got the gist of what you are striving to do, and being Sunday morning :-)

Open the attachment, and save it, so that you can have a practice on this data, before you attempt doing it on your own.

Select cells A1 to A8
Select the menu choice Data
Select the menu choice Text to Columns

A dialgue box will open

Select the option Delimited and click on Next

In the step 2 of 3 dialogue box, make sure that the only option ticked is the Comma box and then click on Next

In the step 3 of 3 dialogue box,you will notice that Excel has already split the data into 2 columns (in the Data preview area).

The first column is highlighted
Click on the second column so that is highlighted and then select the option "Do not import column (skip)"
Notice that the heading changes to Skip column.

You can do one of the following at this point.

The first is to have Excel place the first column that has been split into cells A1 to A8, so that you are only left with the data you need.
If this is what you want, just click on Finish. You will only have the data you want in column A

The second option, is to tell excel to place the data into another column and leave column A intact. If this is wjhat you want, then where it says Destination, change the cell reference to say $D$1. You then click on Finish and the data that you want is placed in column D, with the original data in column A left intact.

Hope that helps. If there is anything you do not understand, get back and let me know, and I will explain further.
Attached Files
File Type: xls texttocolumns.xls (14.0 KB, 268 views)
Couriant's Avatar
Distinguished Member with 27,145 posts.
 
Join Date: Mar 2002
Location: Chillin in AZ
Experience: B.S. in M.I.S
25-May-2008, 08:28 PM #7
I understand how .csv files works. The only problem I have is that Excel, at least Excel 2007 that I am using, understands that the file is a csv file and puts the data in columns, and not all on rows like your example is.

If it was like that, it would make my life more simpler
__________________
Marlene Porter aka. Angelize56 - July 21st 1956 to July 14th 2007 -- Rest in peace Angelcakes :*(
I just had a newborn... so please wait while I try to get a chance to get online.
DO NOT send me email, or request MSN Messenger help, they will be canceled and blocked.
Couriant's Avatar
Distinguished Member with 27,145 posts.
 
Join Date: Mar 2002
Location: Chillin in AZ
Experience: B.S. in M.I.S
26-May-2008, 04:32 AM #8
After viewing your file, I played around with the original file... what I could do is to get the client to open the file in notepad, then do a Find and Replace and replace all , with # (or something). Then save the file and reopen it in Excel. That will give me the same format as your test file so now the client can then use the Text to Columns function and remove the ones she does not need.

But note that the client is not as tech savvy as us so that's why I came here, to see if there is an easier way
__________________
Marlene Porter aka. Angelize56 - July 21st 1956 to July 14th 2007 -- Rest in peace Angelcakes :*(
I just had a newborn... so please wait while I try to get a chance to get online.
DO NOT send me email, or request MSN Messenger help, they will be canceled and blocked.
The Villan's Avatar
Senior Member with 2,003 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
Experience: Advanced at times
26-May-2008, 06:02 AM #9
Can the Macro techies not work what you just did, into a user freindly front end, so that all the user has to do is click on any option (column) that they need or not need.
I am sure that something nice could be done.
I don't do macro's any more so cannot help you further.
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
27-May-2008, 02:26 PM #10
I guess the question remaining is, do you want the users to work this from Excel, and if so do you want them to have a file dialog picker to choose the csv file and import/clean?
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
27-May-2008, 03:04 PM #11
Well, if you already have the name of the file, you could use something like this, which takes a csv file, imports and separates into columns in a new workbook, then deletes the columns you've specified by looking at the header text...
Code:
Option Explicit

Public gEventsToggled As Boolean

Property Let EventsToggled(ByVal TempEvents As Boolean)
    gEventsToggled = TempEvents
End Property

Property Get EventsToggled() As Boolean
    EventsToggled = True
End Property

Sub CallMyImportRoutine()
'Alter file name here...
    Call ImportTextFile("C:\Users\Zack\Desktop\Book2.csv", ",")
End Sub

Public Sub ImportTextFile(sFileName As String, sDelim As String)
'Originally from http://cpearson.com/excel/ImpText.aspx
    Dim wb As Workbook, ws As Worksheet
    Dim iCol As Long, iRow As Long, iPos As Long, iNext As Long
    Dim sLine As String, vTemp As Variant, iSaveCol As Long
        Call TOGGLEEVENTS(False)
        On Error GoTo EndMacro
    Set wb = Workbooks.Add(xlWBATWorksheet)
    Set ws = wb.Sheets(1)
    iRow = 1
    iSaveCol = 1
    Open sFileName For Input Access Read As #1
    While Not EOF(1)
        Line Input #1, sLine
        If Right(sLine, 1) <> sDelim Then sLine = sLine & sDelim
        iCol = iSaveCol
        iPos = 1
        iNext = InStr(iPos, sLine, sDelim)
        While iNext >= 1
            vTemp = Mid(sLine, iPos, iNext - iPos)
            ws.Cells(iRow, iCol).Value = vTemp
            iPos = iNext + 1
            iCol = iCol + 1
            iNext = InStr(iPos, sLine, sDelim)
        Wend
        iRow = iRow + 1
    Wend
    Call CleanUpMyFile(ws)
    ws.Cells.EntireColumn.AutoFit
EndMacro:
    On Error GoTo 0
    Call TOGGLEEVENTS(True)
    Close #1
End Sub

Public Sub CleanUpMyFile(wks As Worksheet)
    Dim iLastCol As Long, i As Long
    If gEventsToggled = False Then Call TOGGLEEVENTS(False)
    iLastCol = wks.Cells(1, wks.Columns.Count).End(xlToLeft).Column
    For i = iLastCol To 1 Step -1
        If wks.Cells(1, i).Value Like "*Limit*" Xor _
           wks.Cells(1, i).Value Like "*Remaining*" Xor _
           wks.Cells(1, i).Value Like "*Last Reset*" Xor _
           wks.Cells(1, i).Value Like "*Machine Serial Number*" Xor _
           wks.Cells(1, i).Value Like "*ID*" Xor _
           wks.Cells(1, i).Value Like "*Account Type*" Xor _
           wks.Cells(1, i).Value Like "*Report*" Then
            wks.Columns(i).Delete
        End If
    Next i
    If gEventsToggled = False Then Call TOGGLEEVENTS(True)
End Sub

Public Sub TOGGLEEVENTS(ByVal blnState As Boolean)
'Originally written by Zack Barresse
    Let EventsToggled = blnState
    With Application
        .DisplayAlerts = blnState
        .EnableEvents = blnState
        .ScreenUpdating = blnState
        If blnState Then .CutCopyMode = False
        If blnState Then .StatusBar = False
    End With
End Sub
This will not alter any existing data, but create an unsaved workbook, but I would still save a copy of your file before you run this just in case. Let us know how it works.
Couriant's Avatar
Distinguished Member with 27,145 posts.
 
Join Date: Mar 2002
Location: Chillin in AZ
Experience: B.S. in M.I.S
28-May-2008, 12:09 PM #12
OK.

What do I save the script as? .bat extension?
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
28-May-2008, 08:25 PM #13
No, this code is VBA, so it goes into an Excel file. The idea is the code would go into an Excel file and your users would somehow, with whatever you choose, run the code on the specified file. The two questions I have is 1) how do you want your users to fire off the code, and 2) do you want your users to choose the file?
Couriant's Avatar
Distinguished Member with 27,145 posts.
 
Join Date: Mar 2002
Location: Chillin in AZ
Experience: B.S. in M.I.S
28-May-2008, 10:23 PM #14
Probably choose because the filename does change as it is based on day and time. Something I can't change and probably a little tedious to change the file name all the time for the client.

So where do I put the code anyways? Like I said, 15 years with Excel, total noob with scripts/macros
__________________
Marlene Porter aka. Angelize56 - July 21st 1956 to July 14th 2007 -- Rest in peace Angelcakes :*(
I just had a newborn... so please wait while I try to get a chance to get online.
DO NOT send me email, or request MSN Messenger help, they will be canceled and blocked.
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
29-May-2008, 03:14 PM #15
Ok, gotcha. One thing I would like to know though, how and where do you want this code to execute from? Is this something you'd like as an Excel add-in? Where it's always available, perhaps its own custom menu item (or Ribbon if you have 2007)? Would you like this to be in a specific file, so users must open it, then run the code? Would you like this in a VBS file where users can double click it and run the program (would take some restructuring)?

We can either walk you through installing the code, or do it for you (i.e. if it's an add-in). From the sounds of it thus far, I'd recommend an add-in, but it's up to you. Before we go any further we'd need to know that.
Reply

Tags
column, delete, excel, macro

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 09:36 PM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.