maraippo
06-30-2008, 09:06 PM
Hi...I am trying to write this macro but still unable to get it right. I'm using MsOffice 2000. I've tried by following few related examples found in this forum, but maybe the way i place it is wrong :-/
when i try to run, it went ok up to dialog to select file, selecting and got
error "13", type mismatch
when click "open"...
basically, what i want to do is, import multiple .smy file to excel worksheet(s) in one workbook. The file is in space delimited form. And if possible to rename the worksheet representing each file to some other name in format 1#n where n=1,2,3...infinite
this is one that i wrote:
====================================
Sub importtext()
Dim Filt As String, Title As String, FilterIndex As Integer, i As Integer, myfile
'Set up list of file filters
Filt = "All Files (*.*),*.*," & _
"Basic Files (*.bas),*.bas," & _
"Class Files (*.cls),*.cls," & _
"Form Files (*.frm),*.frm," & _
"Summary Files (*.smy),*.smy,"
'Display *.* by default
FilterIndex = 5
'Set the Dialog Caption
Title = "Select a File to Import"
'Get The File Name(s)
myfile = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title, _
MultiSelect:=True)
If TypeName(FileName) = "Boolean" Then Exit Sub
For i = LBound(myfile) To UBound(myfile)
' Open the Text file with the OpenText method.
Workbooks.OpenText FileName:=myfile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=True, _
Other:=False, FieldInfo:=Array(1, 1)
With Workbooks("trial.xls")
ActiveSheet.Move After:=.Worksheets(.Worksheets.Count)
End With
Next
End Sub
==========================================
I have wrote a macro before this by selecting single file.The selecting single file works fine..lines above is adjustment from the single file version i made earlier. Macro before this what i do is loop it again and again for user to add their file until they press cancel. But when total file is 40, its tiring doing it again and again.. :(
I'm quite noob in vba and hoping to get some guidance from more experienced user here :)
Thanks in advance :D
-maraippo-
when i try to run, it went ok up to dialog to select file, selecting and got
error "13", type mismatch
when click "open"...
basically, what i want to do is, import multiple .smy file to excel worksheet(s) in one workbook. The file is in space delimited form. And if possible to rename the worksheet representing each file to some other name in format 1#n where n=1,2,3...infinite
this is one that i wrote:
====================================
Sub importtext()
Dim Filt As String, Title As String, FilterIndex As Integer, i As Integer, myfile
'Set up list of file filters
Filt = "All Files (*.*),*.*," & _
"Basic Files (*.bas),*.bas," & _
"Class Files (*.cls),*.cls," & _
"Form Files (*.frm),*.frm," & _
"Summary Files (*.smy),*.smy,"
'Display *.* by default
FilterIndex = 5
'Set the Dialog Caption
Title = "Select a File to Import"
'Get The File Name(s)
myfile = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title, _
MultiSelect:=True)
If TypeName(FileName) = "Boolean" Then Exit Sub
For i = LBound(myfile) To UBound(myfile)
' Open the Text file with the OpenText method.
Workbooks.OpenText FileName:=myfile, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=True, _
Other:=False, FieldInfo:=Array(1, 1)
With Workbooks("trial.xls")
ActiveSheet.Move After:=.Worksheets(.Worksheets.Count)
End With
Next
End Sub
==========================================
I have wrote a macro before this by selecting single file.The selecting single file works fine..lines above is adjustment from the single file version i made earlier. Macro before this what i do is loop it again and again for user to add their file until they press cancel. But when total file is 40, its tiring doing it again and again.. :(
I'm quite noob in vba and hoping to get some guidance from more experienced user here :)
Thanks in advance :D
-maraippo-