Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! :) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing/saving multiple files in Excel w/ macro
Sensational. Thanks a lot!
Mike M |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel creates multiple temp files when saving | Excel Discussion (Misc queries) | |||
Importing and merging Excel files by using a macro | Excel Discussion (Misc queries) | |||
Importing data from multiple excel files | Excel Discussion (Misc queries) | |||
Importing from multiple Excel files | Excel Discussion (Misc queries) | |||
Importing multiple Text files into Excel 2003 | Excel Discussion (Misc queries) |