[Couriant]

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]

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]

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]

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]

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.

[The Villan]

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]

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]

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]

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]

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]

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]

OK.

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

[Zack Barresse]

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]

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]

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.