Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening Files based on list
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening Files based on list
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) "Mike Etzkorn" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening Files based on list
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mike Etzkorn" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening Files based on list
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel files with list function not opening in Excel 2000 | Excel Discussion (Misc queries) | |||
show most recent files first when opening excel files | Excel Discussion (Misc queries) | |||
Opening Quattro Pro for Windows files (*.WB1 Files) using Excel 20 | Excel Discussion (Misc queries) | |||
Combo box list w/ data in 2nd workbook without opening both files | Excel Worksheet Functions | |||
How can I view files chronologically when opening multiple files | Excel Discussion (Misc queries) |