# Creating Macro/script in Excel



## Couriant (Mar 26, 2002)

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


----------



## OBP (Mar 8, 2005)

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?


----------



## Zack Barresse (Jul 25, 2004)

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 (May 24, 2008)

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 (Mar 26, 2002)

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

```
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 [URL=http://forums.techguy.org/business-applications/710174-excel-manipulating-data.html]this thread[/URL], which OBP has posted too.
```


----------



## The Villan (Feb 20, 2006)

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.


----------



## Couriant (Mar 26, 2002)

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


----------



## Couriant (Mar 26, 2002)

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


----------



## The Villan (Feb 20, 2006)

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 (Jul 25, 2004)

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 (Jul 25, 2004)

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...

```
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 (Mar 26, 2002)

OK.

What do I save the script as? .bat extension?


----------



## Zack Barresse (Jul 25, 2004)

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 (Mar 26, 2002)

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


----------



## Zack Barresse (Jul 25, 2004)

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.


----------



## Couriant (Mar 26, 2002)

Zack Barresse said:


> 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.


Hmm... decisions decisions... 

Actually I think an 'add-in' would be better for the client. The simpler the better.


----------



## Zack Barresse (Jul 25, 2004)

Okay, so let's get the UI out of the way. What version(s) of Excel do you want to run this on? Put it this way, are you 97-2003, and if so what is the earliest version this will run on.. or do you run 2007? Do you want a custom menu, custom toolbar, custom ribbon? Do you have faceID preferences (button icons) and/or button text preferences? The file you presented is in 97-2003 format, so I'll assume you're _not_ working with 2007. I don't think we're doing anything that won't run in all three versions, but it would be nice to know.

Oh, and what do you want it to be called?

Edit: And btw, yes I did notice you said _you_ were using 2007, but don't know if your users are. We _can_ do this in 2007 custom Ribbon if you'd like.


----------



## Couriant (Mar 26, 2002)

The client is using MSO 2003, and I have MSO XP (2002) as well as 2007. I can use my earlier version for this instance.


----------



## Zack Barresse (Jul 25, 2004)

Okay, make available for 2002. Gotcha. While I'm coding this up, is there anything else you wish to input about your UI? I.e. what the buttons should be called or anything?


----------



## Couriant (Mar 26, 2002)

Whatever you feel a novice would understand


----------



## Zack Barresse (Jul 25, 2004)

Okay, check out the file. It's pretty basic, let us know what you think. I called it "CSV Importer". Of course you can change to whatever you like. I can post the code to this thread if you like, just let me know.

HTH


----------



## Zack Barresse (Jul 25, 2004)

Oh! I forgot to add this code to the ThisWorkbook module...


```
Option Explicit

Private Sub Workbook_AddinInstall()
    Call CreateMenu
End Sub

Private Sub Workbook_AddinUninstall()
    Call DeleteMenu
End Sub
```
Then you need to install the add-in (as opposed to opening it).


----------



## Couriant (Mar 26, 2002)

Zack Barresse said:


> Oh! I forgot to add this code to the ThisWorkbook module...
> 
> 
> ```
> ...


Where do I put this code?


----------



## Zack Barresse (Jul 25, 2004)

If you have the file open (as opposed to installing the add-in... if you have it installed, after saving the file uninstall it, close and restart Excel) in the VBE (Alt + F11), ensure you have the Project Explorer open (Ctrl +R). Find the file (CSV Importer.xla) and expand it, expand the folder and look for the *ThisWorkbook* module. Double click that and paste the code in that module. Save and close out Excel.

As stated above, if you have the file already installed, save it with the changes, uninstall it, then reinstall it. If you do not have it installed and you just opened the file, just save and close the file then install the add-in. If it's easier I could put the code in the file and repost. Sorry bout the confusion.


----------



## Couriant (Mar 26, 2002)

Is doing right click small icon next to File the same as ThisWorkbook Module?


----------



## Zack Barresse (Jul 25, 2004)

Yes, right clicking that icon and selecting View Code (Excel 97-2003) will bring up the ThisWorkbook module. Remember though, if you're trying this with an add-in, you won't be in the right module - not even the right workbook. Add-ins have a visible property set to False, which is the big difference between them and a normal workbook. You must do it from the VBE to access its ThisWorkbook module.


----------



## Zack Barresse (Jul 25, 2004)

Did this ever work for you?


----------

