Visual Basic Help

Generali

I'm trying to write a simple macro to open 265 reports in turn, format them and save them to a different location. I've used something someone else has 'written' (I'm guessing recorded) which uses the following format:

Workbooks.OpenText Filename:="H:\Reports\xxxxxxOS.TXT", Origin:=932 _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers _
:=True
Cells.Select
Selection.ColumnWidth = 14.86
Range("A1").Select
ChDir "S:\IMS\Reporting\Month End Reporting\Month end reports"
ActiveWorkbook.SaveAs Filename:= _
"S:\IMS\Reporting\Month End Reporting\Month end reports\xxxxxxOS.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Range("F25").Select

I have 265 different files to apply the above to with file names that do not follow any particular pattern. At the moment I am typing over xxxxxxOS with each file name but obviously that is massively laborious. Is there some code I can put in so that there is a list of the xxxxxxOS that the program will refer to using some sort of repeating loop (repeat until? For...next).

I am a very unskilled programmer but have managed to pick up a little VB along the way.

DVardysShadow

Yes there is. I don't do VB if I can possibly avoid it, so I am not going to give you code, but just tell you how I would go about it.

As you say, it is a loop you require. You also need a list of the files, ideally 1 per line. You need to read the file one line at a time as part of your loop - and treat the line as a variable representing the name of a file to which you apply your macro above.

I would start just by reading the file in 1 line at a time to a variable and printing the variable out together with a few *** into a text box and take it from there. Doing it that way would break the problem down so that when it does not work, you do not have to debug over too wide an area.

An extension to this would be to have all you files in a folder and capture the names to a list, then print them out

danthemoneysavingman

if said file doesn't exist already, you'll need to enter the filenames anyway.
You could simply put them in an array and use the loop index to get the next filename.
use something like filenm=PATH&filnames{i}
PATH is the full path part of the file location including final slash.

HTH

Generali

Thanks I think. I'll have to experiment.

So the syntax would be

....filename=H:\Reports\&filenames

where filenames is the array holding the names of the files. Is that right?

DVardysShadow

If you go with Dan tmsm, you might as well put the fully qualified filename in every element of the array. He is right, you can use an array, rather than use a file to list teh files you have to process.

To capture the list of files if they are all in one directory:

dir *OS.TXT /b > dir.lst

from the command line will generate a bare listing of all files xxxxxxOS.TXT in the current directory into a file dir.lst

loudcox

Try something like the following. All I've done is wrap your code in a loop that does it for every file in the directory H:\Reports. Note EVERY file, so ensure that only the necessary files are in the directory. Also, do ensure that you take a backup of the files before running!

Public Sub FormatFiles()


'Directory containing all the report files
Const DirectoryName As String = "H:\Reports"


Dim oFSO As Object
Dim oDir As Object
Dim oFile As Object
Dim wbFormat As Workbook
Set fso = New Scripting.FileSystemObject

'List all the files in the folder
Set oDir = fso.GetFolder(DirectoryName)

For Each oFile In oDir.Files
Workbooks.OpenText Filename:=oFile.Name, Origin:=932, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers _
:=True
Cells.Select
Selection.ColumnWidth = 14.86
Range("A1").Select


ActiveWorkbook.SaveAs Filename:= _
"S:\IMS\Reporting\Month End Reporting\Month end reports\" & oFile.ShortName & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close False

Next oFile

Set fso = Nothing

End Sub

danthemoneysavingman

there you go, someone with more time than me

Generali

Thanks very much everyone, especially loudcox. I'll give it a go at work tomorrow (I'm in Australia) and let you know how I get on.