Multiple Files to Import

[Himansu]

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)."

 

[dolivastro]

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

 

[Ron de Bruin]

Maybe this
http://www.rondebruin.nl/csv.htm

 

[Himansu]

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?

 

[dolivastro]

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]

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

 

[dolivastro]

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]

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

 

[Dave Peterson]

Don't use the Set statement there.

'no ()'s in the next line
workbooks.opentext filename:=....
set newb = activeworkbook