Solved: Importing multiple space delimited text file

[maraippo]

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-

[JimmyTheHand]

At first look, this error can be pointed out:

Workbooks.OpenText FileName:=myfile, Origin:=xlWindows, _
'change it to
Workbooks.OpenText FileName:=myfile(i), Origin:=xlWindows, _

As for renaming the sheets, (to me) it's not clear what you want to achieve.

Jimmy

[maraippo]

Thanks a LOT Jimmy. That has solved my importing text file problem. :)

Regarding renaming the sheets, here is example of the file name:


TCK_02_B82928_MM23SC4428V4_Sort
TCK_02_B82928_MM23SC4428V4_ (2)
TCK_02_B82928_MM23SC4428V4_ (3)
As you can see the name is too long and filling the whole tab and quite hard to browse. So i am looking for a way to rename all the sheets to a shorter name, as 1#2, 1#3, 1#4....1#n in the same macro.

The flow will be: run macro>select file>import file>rename added sheets

Ask me again if you still unclear about this... :)

Thanks

[JimmyTheHand]

Something like this might do...

With Workbooks("trial.xls")
ActiveSheet.Name = "1"
ActiveSheet.Move After:=.Worksheets(.Worksheets.Count)
End With

This is in fact renaming the sheet before adding it to the workbook, and leaves the sheet indices to Excel. So hte sheet names will (probably, as I didn't test it) be

1, 1 (2), 1 (3), 1 (4), etc.

[maraippo]

Thanks jimmy, you've been a lot of help. Appreciate it..

will try the code :)

edit: the code u give solved my prob...thanks :)

[maraippo]

With Workbooks("trial.xls")
ActiveSheet.Name = "1"
ActiveSheet.Move After:=.Worksheets(.Worksheets.Count)
End With

With above code i can only import the file to trial.xls file only rite?

how to edit the code so that the macro can run on any file it run from, not just from trial.xls...

thank you :)

[maraippo]

Solved...

changed to

With ThisWorkbook
ActiveSheet.Name = "1"
ActiveSheet.Move After:=.Worksheets(.Worksheets.Count)
End With