Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Files to Import
Hello everyone, I have multiple .txt files that I import into excel and perform various calculations. I'm trying to automate this process, but here's the problem: ** There might be one .txt file or multiple .txt files that I import (depending on the project). Each worksheet represents an imported .txt file. If there are 7 worksheets then 7 files were inported. What is the best method of automating this? Should the .txt files be a variable? How can I accomplish this? ** Any help will be greatly appreciated. -- Thanks, Himansu "Power tends to corrupt, and absolute power corrupts absolutely (Lord Acton, 1887)." |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Files to Import
If you already know the number and names of the text files, then I
would do the following in VBA: Public Sub AddSheet dim b as Excel.Workbook set b = ActiveWorkbook call ImportText (b, "<File_1") call ImportText (b, "<File_2") call ImportText (b, "<File_3") .... End Sub Private Sub ImportText (b as Excel.Workbook, FileName as string) dim x as Excel.Workbook dim s as Excel.Worksheet set x = Excel.Application.Workbooks.open(FileName, ...) call ActiveSheet.cells.copy set s = b.Worksheets.Add s.Name = FileName call s.cells(1,1).activate call s.Paste x.Close end sub Hope this helps, Dom Himansu wrote: Hello everyone, I have multiple .txt files that I import into excel and perform various calculations. I'm trying to automate this process, but here's the problem: ** There might be one .txt file or multiple .txt files that I import (depending on the project). Each worksheet represents an imported .txt file. If there are 7 worksheets then 7 files were inported. What is the best method of automating this? Should the .txt files be a variable? How can I accomplish this? ** Any help will be greatly appreciated. -- Thanks, Himansu "Power tends to corrupt, and absolute power corrupts absolutely (Lord Acton, 1887)." |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Files to Import
Maybe this
http://www.rondebruin.nl/csv.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Himansu" wrote in message ... Hello everyone, I have multiple .txt files that I import into excel and perform various calculations. I'm trying to automate this process, but here's the problem: ** There might be one .txt file or multiple .txt files that I import (depending on the project). Each worksheet represents an imported .txt file. If there are 7 worksheets then 7 files were inported. What is the best method of automating this? Should the .txt files be a variable? How can I accomplish this? ** Any help will be greatly appreciated. -- Thanks, Himansu "Power tends to corrupt, and absolute power corrupts absolutely (Lord Acton, 1887)." |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Files to Import
Dom,
Ths makes sense, but I would have to change the code manually fo reach job. I might have 3 files todat and 5 files on some other day. The name would be: hha_text1.txt hha_text2.txt hha_text3.txt ....etc. Can I make hha_text%.txt a varabile and perform the calculations for each worksheet? -- Thanks, Himansu wrote in message ups.com... If you already know the number and names of the text files, then I would do the following in VBA: Public Sub AddSheet dim b as Excel.Workbook set b = ActiveWorkbook call ImportText (b, "<File_1") call ImportText (b, "<File_2") call ImportText (b, "<File_3") ... End Sub Private Sub ImportText (b as Excel.Workbook, FileName as string) dim x as Excel.Workbook dim s as Excel.Worksheet set x = Excel.Application.Workbooks.open(FileName, ...) call ActiveSheet.cells.copy set s = b.Worksheets.Add s.Name = FileName call s.cells(1,1).activate call s.Paste x.Close end sub Hope this helps, Dom Himansu wrote: Hello everyone, I have multiple .txt files that I import into excel and perform various calculations. I'm trying to automate this process, but here's the problem: ** There might be one .txt file or multiple .txt files that I import (depending on the project). Each worksheet represents an imported .txt file. If there are 7 worksheets then 7 files were inported. What is the best method of automating this? Should the .txt files be a variable? How can I accomplish this? ** Any help will be greatly appreciated. -- Thanks, Himansu "Power tends to corrupt, and absolute power corrupts absolutely (Lord Acton, 1887)." |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Files to Import
I would use the OpenFilename method. This will show the standard
dialog that Excel uses to allow a user to open files. Only you will doctor it so that the user can open multiple txt files. Like this: Dim Files as Variant Files = GetOpenFilename ("Text Files (*.txt), *.txt, All Files (*.*), *.*", 1, "Open Text Files", , True) If (VarType(Files) = vbBoolean) Then Exit Sub Some points: 1. The variable "Files" must be variant. It will be equal to FALSE if the user cancels out of the dialog box, or it will be an array of strings (of file names) if the user picks one or more names. 2. The last argument, set to TRUE, is important, since it will allow the user to hole the control key and pick multiple files. 3. The line "If (VarType (Files) ..." catches the case in which the user hits the cancel button. After the call to GetOpenFilename, the variable Files is an array of File names, so you can use: Dim F as Variant For each F in Files Process F. F is the name of a file that the user has chosen next F Hope this helps, Dom Himansu wrote: Dom, Ths makes sense, but I would have to change the code manually fo reach job. I might have 3 files todat and 5 files on some other day. The name would be: hha_text1.txt hha_text2.txt hha_text3.txt ...etc. Can I make hha_text%.txt a varabile and perform the calculations for each worksheet? -- Thanks, Himansu wrote in message ups.com... If you already know the number and names of the text files, then I would do the following in VBA: Public Sub AddSheet dim b as Excel.Workbook set b = ActiveWorkbook call ImportText (b, "<File_1") call ImportText (b, "<File_2") call ImportText (b, "<File_3") ... End Sub Private Sub ImportText (b as Excel.Workbook, FileName as string) dim x as Excel.Workbook dim s as Excel.Worksheet set x = Excel.Application.Workbooks.open(FileName, ...) call ActiveSheet.cells.copy set s = b.Worksheets.Add s.Name = FileName call s.cells(1,1).activate call s.Paste x.Close end sub Hope this helps, Dom Himansu wrote: Hello everyone, I have multiple .txt files that I import into excel and perform various calculations. I'm trying to automate this process, but here's the problem: ** There might be one .txt file or multiple .txt files that I import (depending on the project). Each worksheet represents an imported .txt file. If there are 7 worksheets then 7 files were inported. What is the best method of automating this? Should the .txt files be a variable? How can I accomplish this? ** Any help will be greatly appreciated. -- Thanks, Himansu "Power tends to corrupt, and absolute power corrupts absolutely (Lord Acton, 1887)." |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Files to Import
Hi Dom,
I'm still having some issues. Your method works, but the files open in different workbooks. Maybe I'm on the wrong page of your mrthod. Here the code: ------------ Sub JDF() ' ' JDF Macro ' Macro recorded 6/14/2006 by hamin ' -- this is for source code counts AFTER output _ -- is complete. There may be multiple lots. This _ ' -- will prompt you for the files -- for each worksheet. ' ' Dim Files As Variant '-- doctor so that the user can open multiple files Dim SaveAsFile As String 'save file Dim iLastRow As Long Dim iCol As Long Dim i As Long Dim F As Variant '--- ChDir "C:\Clients\bc\JDF" MsgBox Prompt:="Select your jdf****_lot*_sourcecode.txt file(s) from the ALPHA host." Files = Application.GetOpenFilename("Text Files,*.txt,All Files(*.*),*.*", 1, "Open Text Files", , True) For Each F In Files If F < "False" Then Workbooks.OpenText Filename:=F, _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _ , Comma:=False, Space:=False, Other:=True, OtherChar:="=", FieldInfo _ :=Array(Array(1, 1), Array(2, 1)) Else Exit Sub End If Columns("A:A").EntireColumn.AutoFit ActiveWindow.Zoom = 85 Rows("1:1").Select Selection.Insert shift:=xlDown Range("A1").Select ActiveCell.FormulaR1C1 = "ClientSourceCode" Range("B1").Select ActiveCell.FormulaR1C1 = "Qty" Range("a1:b1").Select Selection.Font.Bold = True With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With Columns("A:A").EntireColumn.AutoFit Range("A1").Select 'ActiveWindow.SmallScroll Down:=123 '---------- 'total '---------- With ActiveCell For i = 1 To 1 iCol = .Column + i - 1 iLastRow = Cells(Rows.Count, iCol).End(xlUp).Row 'Selection.Delete shift:=xlDown 'Selection.Delete shift:=xlDown If iLastRow ActiveCell.Row Then Cells(iLastRow + 1, iCol).FormulaR1C1 = _ "Total" Cells(iLastRow + 1, iCol).Font.Bold = True With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End If Next i End With Cells.Select Selection.Delete shift:=xlUp Selection.Delete shift:=xlUp ActiveWindow.ScrollRow = 1 Range("A1").Select ActiveWindow.SmallScroll Down:=66 ActiveWindow.ScrollRow = 105 ActiveWindow.ScrollRow = 1 Windows("jdf1012_lot1_sourcecode.txt").Activate Sheets("jdf1012_lot1_sourcecode").Select Sheets("jdf1012_lot1_sourcecode").Name = "Lot1" Sheets("Lot1").Select Sheets.Add Sheets("Sheet1").Select Sheets("Sheet1").Name = "Lot2" ActiveWorkbook.SaveAs Filename:= _ "C:\Clients\Barton Cotton\JDF\JDF1012\JDF1012_ClientSourceCode.xls", _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWindow.Close ChDir "C:\Clients\bc\JDF" Next F End Sub ------------------------------- **This isn't finished. Hopefully this makes sense. -- Thanks, Himansu wrote in message ups.com... I would use the OpenFilename method. This will show the standard dialog that Excel uses to allow a user to open files. Only you will doctor it so that the user can open multiple txt files. Like this: Dim Files as Variant Files = GetOpenFilename ("Text Files (*.txt), *.txt, All Files (*.*), *.*", 1, "Open Text Files", , True) If (VarType(Files) = vbBoolean) Then Exit Sub Some points: 1. The variable "Files" must be variant. It will be equal to FALSE if the user cancels out of the dialog box, or it will be an array of strings (of file names) if the user picks one or more names. 2. The last argument, set to TRUE, is important, since it will allow the user to hole the control key and pick multiple files. 3. The line "If (VarType (Files) ..." catches the case in which the user hits the cancel button. After the call to GetOpenFilename, the variable Files is an array of File names, so you can use: Dim F as Variant For each F in Files Process F. F is the name of a file that the user has chosen next F Hope this helps, Dom Himansu wrote: Dom, Ths makes sense, but I would have to change the code manually fo reach job. I might have 3 files todat and 5 files on some other day. The name would be: hha_text1.txt hha_text2.txt hha_text3.txt ...etc. Can I make hha_text%.txt a varabile and perform the calculations for each worksheet? -- Thanks, Himansu wrote in message ups.com... If you already know the number and names of the text files, then I would do the following in VBA: Public Sub AddSheet dim b as Excel.Workbook set b = ActiveWorkbook call ImportText (b, "<File_1") call ImportText (b, "<File_2") call ImportText (b, "<File_3") ... End Sub Private Sub ImportText (b as Excel.Workbook, FileName as string) dim x as Excel.Workbook dim s as Excel.Worksheet set x = Excel.Application.Workbooks.open(FileName, ...) call ActiveSheet.cells.copy set s = b.Worksheets.Add s.Name = FileName call s.cells(1,1).activate call s.Paste x.Close end sub Hope this helps, Dom Himansu wrote: Hello everyone, I have multiple .txt files that I import into excel and perform various calculations. I'm trying to automate this process, but here's the problem: ** There might be one .txt file or multiple .txt files that I import (depending on the project). Each worksheet represents an imported .txt file. If there are 7 worksheets then 7 files were inported. What is the best method of automating this? Should the .txt files be a variable? How can I accomplish this? ** Any help will be greatly appreciated. -- Thanks, Himansu "Power tends to corrupt, and absolute power corrupts absolutely (Lord Acton, 1887)." |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Files to Import
Hi Himansu:
Two points: 1. After the call to GetOpenFilename, you want to check the return and exit at that point. Eg, Files = Application.GetOpenFilename (...) if (varType (Files) = vbBoolean) then exit sub This is confusing, but Files (which is a variant) will be acualized as a Boolean if the user click the cancel button. Otherwise, it will be actualized as an array, containing the file names. 2. The OpenText method always returns a new book, never a new sheet. You can't even open a file in a sheet using the Excel menus. So I do this: dim b as Workbook dim NewB as WOrkbook set b = activeWorkbook set NewB = Workbooks.OpenText (...) call ActiveSheet.cells.copy set s = b.Worksheets.Add s.Name = FileName call s.cells(1,1).activate call s.Paste NewB.Close Hope this helps, Dom Himansu wrote: Hi Dom, I'm still having some issues. Your method works, but the files open in different workbooks. Maybe I'm on the wrong page of your mrthod. Here the code: ------------ Sub JDF() ' ' JDF Macro ' Macro recorded 6/14/2006 by hamin ' -- this is for source code counts AFTER output _ -- is complete. There may be multiple lots. This _ ' -- will prompt you for the files -- for each worksheet. ' ' Dim Files As Variant '-- doctor so that the user can open multiple files Dim SaveAsFile As String 'save file Dim iLastRow As Long Dim iCol As Long Dim i As Long Dim F As Variant '--- ChDir "C:\Clients\bc\JDF" MsgBox Prompt:="Select your jdf****_lot*_sourcecode.txt file(s) from the ALPHA host." Files = Application.GetOpenFilename("Text Files,*.txt,All Files(*.*),*.*", 1, "Open Text Files", , True) For Each F In Files If F < "False" Then Workbooks.OpenText Filename:=F, _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _ , Comma:=False, Space:=False, Other:=True, OtherChar:="=", FieldInfo _ :=Array(Array(1, 1), Array(2, 1)) Else Exit Sub End If Columns("A:A").EntireColumn.AutoFit ActiveWindow.Zoom = 85 Rows("1:1").Select Selection.Insert shift:=xlDown Range("A1").Select ActiveCell.FormulaR1C1 = "ClientSourceCode" Range("B1").Select ActiveCell.FormulaR1C1 = "Qty" Range("a1:b1").Select Selection.Font.Bold = True With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With Columns("A:A").EntireColumn.AutoFit Range("A1").Select 'ActiveWindow.SmallScroll Down:=123 '---------- 'total '---------- With ActiveCell For i = 1 To 1 iCol = .Column + i - 1 iLastRow = Cells(Rows.Count, iCol).End(xlUp).Row 'Selection.Delete shift:=xlDown 'Selection.Delete shift:=xlDown If iLastRow ActiveCell.Row Then Cells(iLastRow + 1, iCol).FormulaR1C1 = _ "Total" Cells(iLastRow + 1, iCol).Font.Bold = True With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End If Next i End With Cells.Select Selection.Delete shift:=xlUp Selection.Delete shift:=xlUp ActiveWindow.ScrollRow = 1 Range("A1").Select ActiveWindow.SmallScroll Down:=66 ActiveWindow.ScrollRow = 105 ActiveWindow.ScrollRow = 1 Windows("jdf1012_lot1_sourcecode.txt").Activate Sheets("jdf1012_lot1_sourcecode").Select Sheets("jdf1012_lot1_sourcecode").Name = "Lot1" Sheets("Lot1").Select Sheets.Add Sheets("Sheet1").Select Sheets("Sheet1").Name = "Lot2" ActiveWorkbook.SaveAs Filename:= _ "C:\Clients\Barton Cotton\JDF\JDF1012\JDF1012_ClientSourceCode.xls", _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWindow.Close ChDir "C:\Clients\bc\JDF" Next F End Sub ------------------------------- **This isn't finished. Hopefully this makes sense. -- Thanks, Himansu wrote in message ups.com... I would use the OpenFilename method. This will show the standard dialog that Excel uses to allow a user to open files. Only you will doctor it so that the user can open multiple txt files. Like this: Dim Files as Variant Files = GetOpenFilename ("Text Files (*.txt), *.txt, All Files (*.*), *.*", 1, "Open Text Files", , True) If (VarType(Files) = vbBoolean) Then Exit Sub Some points: 1. The variable "Files" must be variant. It will be equal to FALSE if the user cancels out of the dialog box, or it will be an array of strings (of file names) if the user picks one or more names. 2. The last argument, set to TRUE, is important, since it will allow the user to hole the control key and pick multiple files. 3. The line "If (VarType (Files) ..." catches the case in which the user hits the cancel button. After the call to GetOpenFilename, the variable Files is an array of File names, so you can use: Dim F as Variant For each F in Files Process F. F is the name of a file that the user has chosen next F Hope this helps, Dom Himansu wrote: Dom, Ths makes sense, but I would have to change the code manually fo reach job. I might have 3 files todat and 5 files on some other day. The name would be: hha_text1.txt hha_text2.txt hha_text3.txt ...etc. Can I make hha_text%.txt a varabile and perform the calculations for each worksheet? -- Thanks, Himansu wrote in message ups.com... If you already know the number and names of the text files, then I would do the following in VBA: Public Sub AddSheet dim b as Excel.Workbook set b = ActiveWorkbook call ImportText (b, "<File_1") call ImportText (b, "<File_2") call ImportText (b, "<File_3") ... End Sub Private Sub ImportText (b as Excel.Workbook, FileName as string) dim x as Excel.Workbook dim s as Excel.Worksheet set x = Excel.Application.Workbooks.open(FileName, ...) call ActiveSheet.cells.copy set s = b.Worksheets.Add s.Name = FileName call s.cells(1,1).activate call s.Paste x.Close end sub Hope this helps, Dom Himansu wrote: Hello everyone, I have multiple .txt files that I import into excel and perform various calculations. I'm trying to automate this process, but here's the problem: ** There might be one .txt file or multiple .txt files that I import (depending on the project). Each worksheet represents an imported .txt file. If there are 7 worksheets then 7 files were inported. What is the best method of automating this? Should the .txt files be a variable? How can I accomplish this? ** Any help will be greatly appreciated. -- Thanks, Himansu "Power tends to corrupt, and absolute power corrupts absolutely (Lord Acton, 1887)." |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Files to Import
Thanks Dom. I think this might work.
** Do you have any idea how I can complete this set clause (sorry for any remedial questions): set NewB = Workbooks.OpenText(...) -- this a variable --e.g.: '--- ChDir "C:\Clients\Barton Cotton\JDF" MsgBox Prompt:="Select your jdf****_lot*_sourcecode.txt file(s) from the ALPHA host." Files = Application.GetOpenFilename("Text Files,*.txt,All Files(*.*),*.*", 1, "Open Text Files", , True) If (VarType(Files) = vbBoolean) Then Exit Sub Dim b As Workbook Dim Newb As Workbook Set b = ActiveWorkbook For Each F In Files If F < "False" Then set NewB = Workbooks.OpenText fileName:=f, _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _ , Comma:=False, Space:=False, Other:=True, OtherChar:="=", FieldInfo _ :=Array(Array(1, 1), Array(2, 1)) Else Exit Sub End If -------- -- Thanks, Himansu wrote in message oups.com... Hi Himansu: Two points: 1. After the call to GetOpenFilename, you want to check the return and exit at that point. Eg, Files = Application.GetOpenFilename (...) if (varType (Files) = vbBoolean) then exit sub This is confusing, but Files (which is a variant) will be acualized as a Boolean if the user click the cancel button. Otherwise, it will be actualized as an array, containing the file names. 2. The OpenText method always returns a new book, never a new sheet. You can't even open a file in a sheet using the Excel menus. So I do this: dim b as Workbook dim NewB as WOrkbook set b = activeWorkbook set NewB = Workbooks.OpenText (...) call ActiveSheet.cells.copy set s = b.Worksheets.Add s.Name = FileName call s.cells(1,1).activate call s.Paste NewB.Close Hope this helps, Dom Himansu wrote: Hi Dom, I'm still having some issues. Your method works, but the files open in different workbooks. Maybe I'm on the wrong page of your mrthod. Here the code: ------------ Sub JDF() ' ' JDF Macro ' Macro recorded 6/14/2006 by hamin ' -- this is for source code counts AFTER output _ -- is complete. There may be multiple lots. This _ ' -- will prompt you for the files -- for each worksheet. ' ' Dim Files As Variant '-- doctor so that the user can open multiple files Dim SaveAsFile As String 'save file Dim iLastRow As Long Dim iCol As Long Dim i As Long Dim F As Variant '--- ChDir "C:\Clients\bc\JDF" MsgBox Prompt:="Select your jdf****_lot*_sourcecode.txt file(s) from the ALPHA host." Files = Application.GetOpenFilename("Text Files,*.txt,All Files(*.*),*.*", 1, "Open Text Files", , True) For Each F In Files If F < "False" Then Workbooks.OpenText Filename:=F, _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _ , Comma:=False, Space:=False, Other:=True, OtherChar:="=", FieldInfo _ :=Array(Array(1, 1), Array(2, 1)) Else Exit Sub End If Columns("A:A").EntireColumn.AutoFit ActiveWindow.Zoom = 85 Rows("1:1").Select Selection.Insert shift:=xlDown Range("A1").Select ActiveCell.FormulaR1C1 = "ClientSourceCode" Range("B1").Select ActiveCell.FormulaR1C1 = "Qty" Range("a1:b1").Select Selection.Font.Bold = True With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With Columns("A:A").EntireColumn.AutoFit Range("A1").Select 'ActiveWindow.SmallScroll Down:=123 '---------- 'total '---------- With ActiveCell For i = 1 To 1 iCol = .Column + i - 1 iLastRow = Cells(Rows.Count, iCol).End(xlUp).Row 'Selection.Delete shift:=xlDown 'Selection.Delete shift:=xlDown If iLastRow ActiveCell.Row Then Cells(iLastRow + 1, iCol).FormulaR1C1 = _ "Total" Cells(iLastRow + 1, iCol).Font.Bold = True With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End If Next i End With Cells.Select Selection.Delete shift:=xlUp Selection.Delete shift:=xlUp ActiveWindow.ScrollRow = 1 Range("A1").Select ActiveWindow.SmallScroll Down:=66 ActiveWindow.ScrollRow = 105 ActiveWindow.ScrollRow = 1 Windows("jdf1012_lot1_sourcecode.txt").Activate Sheets("jdf1012_lot1_sourcecode").Select Sheets("jdf1012_lot1_sourcecode").Name = "Lot1" Sheets("Lot1").Select Sheets.Add Sheets("Sheet1").Select Sheets("Sheet1").Name = "Lot2" ActiveWorkbook.SaveAs Filename:= _ "C:\Clients\Barton Cotton\JDF\JDF1012\JDF1012_ClientSourceCode.xls", _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWindow.Close ChDir "C:\Clients\bc\JDF" Next F End Sub ------------------------------- **This isn't finished. Hopefully this makes sense. -- Thanks, Himansu wrote in message ups.com... I would use the OpenFilename method. This will show the standard dialog that Excel uses to allow a user to open files. Only you will doctor it so that the user can open multiple txt files. Like this: Dim Files as Variant Files = GetOpenFilename ("Text Files (*.txt), *.txt, All Files (*.*), *.*", 1, "Open Text Files", , True) If (VarType(Files) = vbBoolean) Then Exit Sub Some points: 1. The variable "Files" must be variant. It will be equal to FALSE if the user cancels out of the dialog box, or it will be an array of strings (of file names) if the user picks one or more names. 2. The last argument, set to TRUE, is important, since it will allow the user to hole the control key and pick multiple files. 3. The line "If (VarType (Files) ..." catches the case in which the user hits the cancel button. After the call to GetOpenFilename, the variable Files is an array of File names, so you can use: Dim F as Variant For each F in Files Process F. F is the name of a file that the user has chosen next F Hope this helps, Dom Himansu wrote: Dom, Ths makes sense, but I would have to change the code manually fo reach job. I might have 3 files todat and 5 files on some other day. The name would be: hha_text1.txt hha_text2.txt hha_text3.txt ...etc. Can I make hha_text%.txt a varabile and perform the calculations for each worksheet? -- Thanks, Himansu wrote in message ups.com... If you already know the number and names of the text files, then I would do the following in VBA: Public Sub AddSheet dim b as Excel.Workbook set b = ActiveWorkbook call ImportText (b, "<File_1") call ImportText (b, "<File_2") call ImportText (b, "<File_3") ... End Sub Private Sub ImportText (b as Excel.Workbook, FileName as string) dim x as Excel.Workbook dim s as Excel.Worksheet set x = Excel.Application.Workbooks.open(FileName, ...) call ActiveSheet.cells.copy set s = b.Worksheets.Add s.Name = FileName call s.cells(1,1).activate call s.Paste x.Close end sub Hope this helps, Dom Himansu wrote: Hello everyone, I have multiple .txt files that I import into excel and perform various calculations. I'm trying to automate this process, but here's the problem: ** There might be one .txt file or multiple .txt files that I import (depending on the project). Each worksheet represents an imported .txt file. If there are 7 worksheets then 7 files were inported. What is the best method of automating this? Should the .txt files be a variable? How can I accomplish this? ** Any help will be greatly appreciated. -- Thanks, Himansu "Power tends to corrupt, and absolute power corrupts absolutely (Lord Acton, 1887)." |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple Files to Import
Don't use the Set statement there.
'no ()'s in the next line workbooks.opentext filename:=.... set newb = activeworkbook Himansu wrote: Thanks Dom. I think this might work. ** Do you have any idea how I can complete this set clause (sorry for any remedial questions): set NewB = Workbooks.OpenText(...) -- this a variable --e.g.: '--- ChDir "C:\Clients\Barton Cotton\JDF" MsgBox Prompt:="Select your jdf****_lot*_sourcecode.txt file(s) from the ALPHA host." Files = Application.GetOpenFilename("Text Files,*.txt,All Files(*.*),*.*", 1, "Open Text Files", , True) If (VarType(Files) = vbBoolean) Then Exit Sub Dim b As Workbook Dim Newb As Workbook Set b = ActiveWorkbook For Each F In Files If F < "False" Then set NewB = Workbooks.OpenText fileName:=f, _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _ , Comma:=False, Space:=False, Other:=True, OtherChar:="=", FieldInfo _ :=Array(Array(1, 1), Array(2, 1)) Else Exit Sub End If -------- -- Thanks, Himansu wrote in message oups.com... Hi Himansu: Two points: 1. After the call to GetOpenFilename, you want to check the return and exit at that point. Eg, Files = Application.GetOpenFilename (...) if (varType (Files) = vbBoolean) then exit sub This is confusing, but Files (which is a variant) will be acualized as a Boolean if the user click the cancel button. Otherwise, it will be actualized as an array, containing the file names. 2. The OpenText method always returns a new book, never a new sheet. You can't even open a file in a sheet using the Excel menus. So I do this: dim b as Workbook dim NewB as WOrkbook set b = activeWorkbook set NewB = Workbooks.OpenText (...) call ActiveSheet.cells.copy set s = b.Worksheets.Add s.Name = FileName call s.cells(1,1).activate call s.Paste NewB.Close Hope this helps, Dom Himansu wrote: Hi Dom, I'm still having some issues. Your method works, but the files open in different workbooks. Maybe I'm on the wrong page of your mrthod. Here the code: ------------ Sub JDF() ' ' JDF Macro ' Macro recorded 6/14/2006 by hamin ' -- this is for source code counts AFTER output _ -- is complete. There may be multiple lots. This _ ' -- will prompt you for the files -- for each worksheet. ' ' Dim Files As Variant '-- doctor so that the user can open multiple files Dim SaveAsFile As String 'save file Dim iLastRow As Long Dim iCol As Long Dim i As Long Dim F As Variant '--- ChDir "C:\Clients\bc\JDF" MsgBox Prompt:="Select your jdf****_lot*_sourcecode.txt file(s) from the ALPHA host." Files = Application.GetOpenFilename("Text Files,*.txt,All Files(*.*),*.*", 1, "Open Text Files", , True) For Each F In Files If F < "False" Then Workbooks.OpenText Filename:=F, _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _ , Comma:=False, Space:=False, Other:=True, OtherChar:="=", FieldInfo _ :=Array(Array(1, 1), Array(2, 1)) Else Exit Sub End If Columns("A:A").EntireColumn.AutoFit ActiveWindow.Zoom = 85 Rows("1:1").Select Selection.Insert shift:=xlDown Range("A1").Select ActiveCell.FormulaR1C1 = "ClientSourceCode" Range("B1").Select ActiveCell.FormulaR1C1 = "Qty" Range("a1:b1").Select Selection.Font.Bold = True With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With Columns("A:A").EntireColumn.AutoFit Range("A1").Select 'ActiveWindow.SmallScroll Down:=123 '---------- 'total '---------- With ActiveCell For i = 1 To 1 iCol = .Column + i - 1 iLastRow = Cells(Rows.Count, iCol).End(xlUp).Row 'Selection.Delete shift:=xlDown 'Selection.Delete shift:=xlDown If iLastRow ActiveCell.Row Then Cells(iLastRow + 1, iCol).FormulaR1C1 = _ "Total" Cells(iLastRow + 1, iCol).Font.Bold = True With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End If Next i End With Cells.Select Selection.Delete shift:=xlUp Selection.Delete shift:=xlUp ActiveWindow.ScrollRow = 1 Range("A1").Select ActiveWindow.SmallScroll Down:=66 ActiveWindow.ScrollRow = 105 ActiveWindow.ScrollRow = 1 Windows("jdf1012_lot1_sourcecode.txt").Activate Sheets("jdf1012_lot1_sourcecode").Select Sheets("jdf1012_lot1_sourcecode").Name = "Lot1" Sheets("Lot1").Select Sheets.Add Sheets("Sheet1").Select Sheets("Sheet1").Name = "Lot2" ActiveWorkbook.SaveAs Filename:= _ "C:\Clients\Barton Cotton\JDF\JDF1012\JDF1012_ClientSourceCode.xls", _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWindow.Close ChDir "C:\Clients\bc\JDF" Next F End Sub ------------------------------- **This isn't finished. Hopefully this makes sense. -- Thanks, Himansu wrote in message ups.com... I would use the OpenFilename method. This will show the standard dialog that Excel uses to allow a user to open files. Only you will doctor it so that the user can open multiple txt files. Like this: Dim Files as Variant Files = GetOpenFilename ("Text Files (*.txt), *.txt, All Files (*.*), *.*", 1, "Open Text Files", , True) If (VarType(Files) = vbBoolean) Then Exit Sub Some points: 1. The variable "Files" must be variant. It will be equal to FALSE if the user cancels out of the dialog box, or it will be an array of strings (of file names) if the user picks one or more names. 2. The last argument, set to TRUE, is important, since it will allow the user to hole the control key and pick multiple files. 3. The line "If (VarType (Files) ..." catches the case in which the user hits the cancel button. After the call to GetOpenFilename, the variable Files is an array of File names, so you can use: Dim F as Variant For each F in Files Process F. F is the name of a file that the user has chosen next F Hope this helps, Dom Himansu wrote: Dom, Ths makes sense, but I would have to change the code manually fo reach job. I might have 3 files todat and 5 files on some other day. The name would be: hha_text1.txt hha_text2.txt hha_text3.txt ...etc. Can I make hha_text%.txt a varabile and perform the calculations for each worksheet? -- Thanks, Himansu wrote in message ups.com... If you already know the number and names of the text files, then I would do the following in VBA: Public Sub AddSheet dim b as Excel.Workbook set b = ActiveWorkbook call ImportText (b, "<File_1") call ImportText (b, "<File_2") call ImportText (b, "<File_3") ... End Sub Private Sub ImportText (b as Excel.Workbook, FileName as string) dim x as Excel.Workbook dim s as Excel.Worksheet set x = Excel.Application.Workbooks.open(FileName, ...) call ActiveSheet.cells.copy set s = b.Worksheets.Add s.Name = FileName call s.cells(1,1).activate call s.Paste x.Close end sub Hope this helps, Dom Himansu wrote: Hello everyone, I have multiple .txt files that I import into excel and perform various calculations. I'm trying to automate this process, but here's the problem: ** There might be one .txt file or multiple .txt files that I import (depending on the project). Each worksheet represents an imported .txt file. If there are 7 worksheets then 7 files were inported. What is the best method of automating this? Should the .txt files be a variable? How can I accomplish this? ** Any help will be greatly appreciated. -- Thanks, Himansu "Power tends to corrupt, and absolute power corrupts absolutely (Lord Acton, 1887)." -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import Multiple XML Files into Excel | Excel Discussion (Misc queries) | |||
import multiple files to excel | Excel Programming | |||
Need to import multiple files with a macro | Excel Programming | |||
Multiple txt files import | Excel Programming | |||
Import multiple files macro can't find files | Excel Programming |