Plz help a VB dummy with his first project...

[defender]

Hi!

I've just begun to see the potential of the VB software built into the Microsoft Office suite and am trying my hands on writing a macro that will automate a process for me.

Here's what I'm trying to do... I have a text file which contains a list of phrases I use for advertising purposes. I want to be able to automatically add another phrase at the end of each line in the text file.

So far I've managed to record a macro to open the original text file, write a formula that creates a new column containing the original phrases with the new phrase, copy and paste this column into a new book and then save that sheet as a new text file.

Here's the code that accomplishes this...

Sub Macro1()
'
' Macro1 Macro
' Macro recorded  by Zahid
'
 
'
    Workbooks.OpenText Filename:= _
        "C:\Documents and Settings\Zahid\My Documents\input.txt" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]&"" london"""
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:B3")
    Range("B1:B3").Select
    Columns("B:B").Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Zahid\My Documents\output.txt", FileFormat:=xlUnicodeText _
        , CreateBackup:=False
End Sub

Now, here's the hard bit...

I need the macro to be able to repeat this process for multiple files!

In other words, I would specify either in the macro, or be prompted somehow, the text files that I need processed. The macro will then process each file and save new text files for each file that it processes.

I guess my question is, is this possible with Visual Basic, and if so, how in the world do I do that?

Thanks in advance!

[DKenny]

....or be prompted somehow, the text files that I need processed.
!

If you want ot be prompted for the file to open, start by reading in the help file on the "Dialog" object. You should probably be using the "xlDialogOpenText" dialog.

....and save new text files for each file that it processes.

Again, you need to use a Dialog object, but this time you would use the "xlDialogSaveAs" dialog.

Start by reading up on these in the help file. Try writing some code (its much better for you if you try on your own first). When it doesn't work, then post it back here for review.


Also, welcome to the forums.

[zaza]

Hi,

Welcome to the Forums. Yes you can, and yes it's pretty easy. I'd start by bringing up a dialog (look up Filedialog in VBA help) to allow the user to select some files. Then you can just put all the code in a For..Next loop to run it as many times as you need.
So you might want, for example:

Dim dlgOpen As FileDialog
    Set dlgOpen = Application.FileDialog( _
        FileDialogType:=msoFileDialogOpen)
    With dlgOpen
        .AllowMultiSelect = True
        .Show
    End With
 
 For Each i in dlgopen.selecteditems
 
 'Insert your code here - changing the filenames to "i"
 
 Next i

Off the top of my head.

zaza

[Webtest]

Sure you can do this in VBA. You'll need the File Selection Browser:

    'Set the File Filter for Adobe PDFs and Excel Workbooks
    ff = "Store Files (*.pdf; *.xls),*.pdf;*.xls"
    'Set the Title in the Browser Dialog Box
    tt = "Browse and Select a single Store-Specific file"
    'Run the File Browser Box to get a prototype file specification
    'Returns "False" if file not selected
    fname = Application.GetOpenFilename(ff, , tt, , False)

GetOpenFilename returns "False" if the select is canceled or if no file is selected. There are many ways to go from here. Set up some kind of loop fetching filenames until the list (or the user!) is exhausted. How are you going to prevent re-selecting a file that has already been used?

[defender]

Wow, this place is just awesome!

Here's what I've got so far...

Sub Macro2()
 
Dim dlgOpen As FileDialog
    Set dlgOpen = Application.FileDialog( _
        FileDialogType:=msoFileDialogOpen)
    With dlgOpen
        .AllowMultiSelect = True
        .Show
    End With
 
 For Each i In dlgOpen.SelectedItems
 
    Workbooks.Open i
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]&"" london"""
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:B3")
    Range("B1:B3").Select
    Columns("B:B").Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
 Next i
  
End Sub

I can now open multiple text files and the macro will create a new workbook for each of these text files just how I want it. However, I'm stuck when it comes to saving each of these files, if I specify the filename (i.e. ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Zahid\My Documents\output.txt", FileFormat:=xlUnicodeText _ , CreateBackup:=False) then this will be overwritten as each file is processed right?

I guess what I need to do is have the macro either prompt me to save each file first, OR even better automatically save the new files somehow. Is there any way I can get the macro to save the file with the original filename but a different file extension? In other words, when it processes 1.txt it will save the output as 1.csv?

Thanks again, if I can do this I'll be jumping for joy all night long!!!

[defender]

Okay, I've managed to get a prompt after each file is processed to save the output. Here's how the code looks...

Sub Macro2()
 
Dim dlgOpen As FileDialog
    Set dlgOpen = Application.FileDialog( _
        FileDialogType:=msoFileDialogOpen)
    With dlgOpen
        .AllowMultiSelect = True
        .Show
    End With
 
 For Each i In dlgOpen.SelectedItems
 
    Workbooks.Open i
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]&"" london"""
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:B500")
    Columns("B:B").Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
        
Do
    fName = Application.GetSaveAsFilename( _
    fileFilter:="Text Files (*.txt), *.txt")
Loop Until fName <> False
    
    ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlUnicodeText _
        , CreateBackup:=False
    
 Next i
  
End Sub

It works great so far, but I REALLY wanna see if I can get this macro to do what I mentioned in the previous post which is save the ouput automatically using the same filename as the original file but a .csv extension.

Any suggestions?

[defender]

It looks like I've one more thing to figure out...

My text files contain up to 500 lines of data, so in order to process them I have used the autofill command with a range from cells B1 to B500 (i.e. Selection.AutoFill Destination:=Range("B1:B500")).

However, by having this, if a text file has only 400 lines then the output will have 100 lines at the bottom which contain the new phrase by itself which I do NOT want. I've been trying to use an IF statement in the formula that writes the output but have not had any luck. Here's the line I'm referring to:

ActiveCell.FormulaR1C1 = "=RC[-1]&"" london"""

I don't quite understand what the R1C1 and the RC[-1] refers to, this part was created by recording the macro, rather than writing it myself.

Any idea how can I get this formula to only write the additional word to each line IF the original phrase exists. So if there are 400 phrases in the text file, it only creates 400 lines of output and leaves the final 100 cells empty?

[DKenny]

Lets take this one step at a time.
Saving as as .csv with the same name is no problem, you need to get the current file name and strip off the last 4 character, the ".txt", and then save it with a .csv extension
if you replace your code

Do
    fName = Application.GetSaveAsFilename( _
    fileFilter:="Text Files (*.txt), *.txt")
Loop Until fName <> False
    
    ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlUnicodeText _
        , CreateBackup:=False

with

With ActiveWorkbook
    .SaveAs Filename:=Left(.Name, Len(.Name) - 4), FileFormat:=xlCSV
    .Close SaveChanges:=False
End With

this will automatically save the file as a csv with the correct name. I've also added a line to close the file after it is saved.

[DKenny]

....if a text file has only 400 lines then the output will have 100 lines at the bottom which contain the new phrase by itself which I do NOT want.

OK, so we need to
1/ determine how far down to go
2/ Select column B down to that cell
3/ Fill down

You need to replace your code

Workbooks.Open I
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]&"" london"""
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B500")
Columns("B:B").Select
Selection.Copy
Workbooks.Add 'I have no idea why you neeed to add a workbook here.
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

with

Workbooks.Open I
Range("B1").FormulaR1C1 = "=RC[-1]&"" london"""
LastRow = Range("A65536").End(xlUp).Row 'Find the row number of the last cell used in column "A"
With Range(Cells(1, 2), Cells(LastRow, 2)) 'Select cells B1 [Cells(1, 2)] to BLastRow [Cells(LastRow, 2)]
    .FillDown
    .Copy
    .PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False

In the above, I use a variable "LastRow" to store the last row used in col A.
Then, selecting the range B1 to BLastrow, I fill the formula down, copy, paste values and clear the clipboard.

If any of this doesn't make sense, let me know.

ActiveCell.FormulaR1C1 = "=RC[-1]&"" london"""

I don't quite understand what the R1C1 and the RC[-1] refers to, this part was created by recording the macro, rather than writing it myself.

FormulaR1C1 means that the formula is being entered in the R1C1 reference mode rather than the A1 reference mode. Check the Excel help files for "About cell and range references "
You can change this to use A1 mode by changing this line of code to

Range("B1").Formula = "=A1" & " london"

[defender]

Wow, you guys are really amazing! I can't believe how helpful you all are!

Declan, I've implemented your suggestions and now have the macro automatically saving each file as a CSV file using the original filename, and the resulting files contains only as many lines as the original. It's beautiful!

I've decided I want to make a small change, however. Instead of processing multiple files, I want to modify the macro to process just one big file that is open, which I'll split into multiple files at a later date.

I've got the macro working now to do just that, however, there's one final bug left. I need to specify the directory to save the CSV files in, I tried to do this myself but ended up messing the code.

Could you help me with this Declan? Here's the new code I've got...

Sub CSVCreator()
 
    Range("B1").FormulaR1C1 = "=RC[-1]&"" london"""
    LastRow = Range("A65536").End(xlUp).Row
    With Range(Cells(1, 2), Cells(LastRow, 2))
        .FillDown
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
 
With ActiveWorkbook
    .SaveAs Filename:=Left(.Name, Len(.Name) - 4), FileFormat:=xlCSV
    .Close SaveChanges:=False
End With
 
Application.Quit
 
End Sub

All I need to do is specify the directory for the output to be stored in, something like "C:\temp\" will do for the time being.

Thanks a GAZILLIAN!

P.S. Is there a way to have the number of rows printed into the filename, or is that too big a task? I.e. keywords500.csv if the original file is keywords.txt and has 500 keywords?

[DKenny]

Defender
You just need to add the path and the row count to the file name in the SaveAs method. I used "C:\temp\", but you can change accordingly

.SaveAs Filename:="C:\temp\"& Left(.Name, Len(.Name) - 4) & LastRow, FileFormat:=xlCSV

One other thing, Check out "Option Explicit" in the help files. You should always use this and always declare your variables. Might as well get into the good practices at the start.

[defender]

OMG! I'm speechless!!!

[defender]

I just wanted to write and thank you all for your incredible support in this project. I really appreciate it!

The mods are free to close this thread and mark it resolved.

[si_the_geek]

I'm glad you got the answer, and are so happy about it

We don't normally close threads, and marking it as resolved is up to you I'm afraid - just above the first post in the thread select "Thread Tools", then "Mark Thread Resolved".