Importing/saving multiple files in Excel w/ macro
 

I am generating data from simulations. Each new simulation has a new folder,
named 'kal****'. Currently I have a macro code to prompt the user to input a
sim number, the macro will then go to the right directory, import the file
which is always named 'diff' (with no extension), make a few formatting
changes to the file, and then save it in DBF 4 format. No probs. My code for
this follows:

Sub diffgis()

Dim kaldirectory As Variant

kaldirectory = InputBox("Enter 'kal' + number", "Kal Number Entry", "kal")
Workbooks.OpenText Filename:="H:\visual_basic\" & kaldirectory &
"\diff", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True,
Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8,
1), Array(9, 1))
Columns("F:H").Select
Selection.NumberFormat = "0.00"
Range("G14").Select
ActiveWorkbook.SaveAs Filename:="H:\visual_basic\" & kaldirectory &
"\diff.dbf", FileFormat:= _
xlDBF4, CreateBackup:=False
ActiveWindow.Close

End Sub


Now, I am generating 3 'diff' files per simulation, 'diff_1983',
'diff_1993', and 'diff_2000'. I want to just be able to input the directory
number as usual, and have the same changes made and all three saved by the
macro. Is there a way of modifying the Workbook.OpenText and
ActiveWorkbook.SaveAs lines to do this to all three files, instead of having
to perform these commands three separate times along with all the true/false
statements and other stuff???? Would be easy to do, but long and ugly code!!

Any help appreciated,
Mike M

Importing/saving multiple files in Excel w/ macro
 

I should add, the 'Range("G14").Select ' line near the end is just my
recorded method for deselecting the cells selected previously, for want of
knowing a deselect command!! :)

Importing/saving multiple files in Excel w/ macro
 

Sub diffgis()
Dim vArr as Variant
Dim kaldirectory As Variant
vArr = Array("_1983","_1993","_2000")
kaldirectory = InputBox("Enter 'kal' + number", "Kal Number Entry",
"kal")
for i = lbound(varr) to ubound(varr)
Workbooks.OpenText Filename:="H:\visual_basic\" & _
kaldirectory & "\diff" & varr(i), Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2,1), Array(3, 1), Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1), Array(8,1), Array(9, 1))
Columns("F:H").Select
Selection.NumberFormat = "0.00"
Range("G14").Select
ActiveWorkbook.SaveAs Filename:= _
"H:\visual_basic\" & kaldirectory & "\diff" & _
varr(i) & ".dbf", FileFormat:=xlDBF4, _
CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False
Next


End Sub

--
Regards,
Tom Ogilvy


"Mike M" wrote in message
...
I am generating data from simulations. Each new simulation has a new
folder,
named 'kal****'. Currently I have a macro code to prompt the user to input
a
sim number, the macro will then go to the right directory, import the file
which is always named 'diff' (with no extension), make a few formatting
changes to the file, and then save it in DBF 4 format. No probs. My code
for
this follows:

Sub diffgis()

Dim kaldirectory As Variant

kaldirectory = InputBox("Enter 'kal' + number", "Kal Number Entry",
"kal")
Workbooks.OpenText Filename:="H:\visual_basic\" & kaldirectory &
"\diff", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
_
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True,
Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1),
Array(8,
1), Array(9, 1))
Columns("F:H").Select
Selection.NumberFormat = "0.00"
Range("G14").Select
ActiveWorkbook.SaveAs Filename:="H:\visual_basic\" & kaldirectory &
"\diff.dbf", FileFormat:= _
xlDBF4, CreateBackup:=False
ActiveWindow.Close

End Sub


Now, I am generating 3 'diff' files per simulation, 'diff_1983',
'diff_1993', and 'diff_2000'. I want to just be able to input the
directory
number as usual, and have the same changes made and all three saved by the
macro. Is there a way of modifying the Workbook.OpenText and
ActiveWorkbook.SaveAs lines to do this to all three files, instead of
having
to perform these commands three separate times along with all the
true/false
statements and other stuff???? Would be easy to do, but long and ugly
code!!

Any help appreciated,
Mike M

Importing/saving multiple files in Excel w/ macro
 

Sensational. Thanks a lot!

Mike M