Hi all!
I'm working on my first excel Macro and after reading through millions of ppl's posts, I can't seem to find one that will work specifically for me. I'm trying to batch process a number of .obj files (space delimited) by having excel open them in the proper format and then automatically save them as a .xls file with the same name. Here's what I've put together so far from what everyone has posted (below), the only problem is it doesn't name them appropriately, it just says filename, filename1, filename 2 etc..
Sub OpenTextFiles()
Dim sFile As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select Directory"
If .Show = 0 Then Exit Sub
End With
Application.DisplayAlerts = False
sFile = Dir("*.obj")
Do While Len(sFile) > 0
Workbooks.OpenText Filename:=sFile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, Semicolon:=False, Comma:=False, _
Space:=True, Other:=False
sFile = Dir()
Set wb = Workbooks(Workbooks.Count)
wb.SaveAs "C:\Users\heather\Desktop\filename" & cnt & ".xls", xlWorkbookNormal
wb.Close
cnt = cnt + 1
Loop
Application.DisplayAlerts = True
End Sub
Any help is greatly appreciated, my obj files are something like 14wfrb, 14wflb, 1932wfrb, etc.
I'm working on my first excel Macro and after reading through millions of ppl's posts, I can't seem to find one that will work specifically for me. I'm trying to batch process a number of .obj files (space delimited) by having excel open them in the proper format and then automatically save them as a .xls file with the same name. Here's what I've put together so far from what everyone has posted (below), the only problem is it doesn't name them appropriately, it just says filename, filename1, filename 2 etc..
Sub OpenTextFiles()
Dim sFile As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select Directory"
If .Show = 0 Then Exit Sub
End With
Application.DisplayAlerts = False
sFile = Dir("*.obj")
Do While Len(sFile) > 0
Workbooks.OpenText Filename:=sFile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, Semicolon:=False, Comma:=False, _
Space:=True, Other:=False
sFile = Dir()
Set wb = Workbooks(Workbooks.Count)
wb.SaveAs "C:\Users\heather\Desktop\filename" & cnt & ".xls", xlWorkbookNormal
wb.Close
cnt = cnt + 1
Loop
Application.DisplayAlerts = True
End Sub
Any help is greatly appreciated, my obj files are something like 14wfrb, 14wflb, 1932wfrb, etc.
