Opening Files based on list

[Guest]

Here is the situation, I am using Elcel XP on a Windows XP machine. I have a spreadsheet that lists all of the files in a directory. It starts in cell B3. This list is text files that I have to convert to excel files. The problem is that the file names change regulary and so does the number of files. I need to get a macro that will open and convert each file, then save it under the same name but as a .xls file.

Here is what I got by recording a macro.

Workbooks.OpenText FileName:="C:\File1010704.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, 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), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
16, 1)), TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs FileName:="C:\File1010704.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

This will open and convert the file corectly and save it but only for that file and since the names change, and the number of fiels cahnges, this woun't work right. I need to get it so that instead of opening the specific file listed that it goes through my list of files and does this process to each of them. Any help would be greatly appreciated. I know how to record macros and do basic stuff but this is a little out of my league.

Thanks for any help,

Mike

 

[Bob Phillips]

Mike,

Here's one solution

Sub AllFiles()
Dim cLastRow As Long
Dim i As Long
Dim sh As Worksheet

Set sh = ActiveWorkbook.ActiveSheet

cLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To cLastRow
UpdateTextFile Left(sh.Cells(i, "B").Value, Len(sh.Cells(i,
"B").Value) - 4)
Next i

End Sub

Sub UpdateTextFile(name As String)
Dim wb As Workbook
On Error Resume Next
Workbooks.OpenText _
Filename:=name & ".txt", _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
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), _
Array(10, 1), Array(11, 1), Array(12, 1), _
Array(13, 1), Array(14, 1), Array(15, 1), _
Array(16, 1))

With ActiveWorkbook
If Left(.FullName, Len(.FullName) - 4) = name Then
Application.DisplayAlerts = False
.SaveAs Filename:=name, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
.Close
Application.DisplayAlerts = True
End If
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Here is the situation, I am using Elcel XP on a Windows XP machine. I

have a spreadsheet that lists all of the files in a directory. It starts in
cell B3. This list is text files that I have to convert to excel files.
The problem is that the file names change regulary and so does the number of
files. I need to get a macro that will open and convert each file, then
save it under the same name but as a .xls file.

Here is what I got by recording a macro.

Workbooks.OpenText FileName:="C:\File1010704.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, 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), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
16, 1)), TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs FileName:="C:\File1010704.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

This will open and convert the file corectly and save it but only for that

file and since the names change, and the number of fiels cahnges, this
woun't work right. I need to get it so that instead of opening the specific
file listed that it goes through my list of files and does this process to
each of them. Any help would be greatly appreciated. I know how to record
macros and do basic stuff but this is a little out of my league.

 

[Bob Phillips]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Ok works great for almost everything. I got it to open my files and

everything and same them, only one thing - I can't get them to save to a
different directory - I have tried a few things but to no avail. It loads
from "RawData", and needs to save in "ConvertedData"

Portion as follows:

With ActiveWorkbook
If Left(.FullName, Len(.FullName) - 4) = name Then
Application.DisplayAlerts = False
ChDir ThisWorkbook.Path & "\" & "ConvertedData" '<--- What I tried to add
.SaveAs FileName:=name, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
.Close
Application.DisplayAlerts = True
End If
End With

Replcae that code portion with this. You might want to change
"C:\ConvertData\" to a more specific directory path.

With ActiveWorkbook
If Left(.FullName, Len(.FullName) - 4) = name Then
Application.DisplayAlerts = False
.SaveAs Filename:="C:ConvertedData\" & .name, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
.Close
Application.DisplayAlerts = True
End If
End With

One thing, as I am trying to learn and understand this code, the "- 4" in

both parts(top and bottom sections), what does it stand for, so that if say
I move my data to start in A1, do I need to change that. And for the file
names in the spreadsheet I have to conconate to add the "c:\" at the fron
and ".txt" to the back, is there a way to do this in the code.
The -4 is to strip of the .txt file extension from the name. If you move
your data to start in A1, you need to change this

cLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To cLastRow
UpdateTextFile Left(sh.Cells(i, "B").Value,
Len(sh.Cells(i,"B").Value) - 4)
Next

to this

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To cLastRow
UpdateTextFile Left(sh.Cells(i, "A").Value,
Len(sh.Cells(i,"A").Value) - 4)
Next

Regards

Bob

 

[Guest]

Thanks so much, it works great, and now I think I understand it enouph to be able to modify it when I need to, which helps me learn how to do more of this on my own, as I learn more about how each code works.

Thanks,

Mike