Problem with importing multiple data files with fixed width format

**sport_logo** · 05-19-2010, 07:30 AM

Hi all,
I have the following code to import multiple files and apply a fixed width formatting to them but the code isn't working. Could someone tell me why?

Sub OpenManyFiles()

        Dim myFile As Variant
    Dim i As Long
    myFile = Application.GetOpenFilename("All Files,*.*", MultiSelect:=True)
    For i = LBound(myFile) To UBound(myFile)
    Workbooks.Open myFile(i)
    
        Workbooks.OpenText FileName:=myFile, _
        Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
        Array(Array(0, 1), Array(7, 1), Array(14, 1), Array(24, 1), Array(31, 1), Array(41, 1), _
        Array(53, 1), Array(62, 1), Array(68, 1), Array(78, 1), Array(86, 1), Array(93, 1), Array( _
        104, 1)), TrailingMinusNumbers:=True
        
        Next i
    
End Sub

thanks in advance

**pike** · 05-20-2010, 06:23 AM

dude what do you mean by "fixed width formatting "?

**sport_logo** · 05-20-2010, 10:19 AM

Hi Pike,

it is the fixed width option on the text import wizard as opposed to the delimited option. I have this bit sorted in the code above but I need some help with applying this to a multiple file import. Does that make sense?
Thanks
sport_logo

**pike** · 05-20-2010, 06:19 PM

so what do you mean by not working?

**sport_logo** · 05-21-2010, 03:51 AM

Hi Pike,

Sorry I haven't provided enough information. The code opens up a "open file" dialog box and I choose the text files I want to open. when I press "open" i get an error message with "run-time error "13": type mismatch. When I press "debug" the code paragraph starting "workbooks.opentext filename:=myFile,........" is highlighted. This paragraph sorts out the row widths of the sheet.

When I do this just opening one text file it works fine so i think it is something to do with trying to apply it to the multiple files.

Thanks for you patience

**Ryan Murtagh** · 05-21-2010, 03:59 AM

What data type did you use to declare the variable myFile? Can you post your code?

**sport_logo** · 05-21-2010, 04:45 AM

the code I use to import a single file is:

Sub ImportDataFile()
'
    Dim myFile As Variant
    myFile = Application.GetOpenFilename("All Files,*.*", MultiSelect:=False)
   
        Workbooks.OpenText FileName:=myFile, _
        Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
        Array(Array(0, 1), Array(7, 1), Array(14, 1), Array(24, 1), Array(31, 1), Array(41, 1), _
        Array(53, 1), Array(62, 1), Array(68, 1), Array(78, 1), Array(86, 1), Array(93, 1), Array( _
        104, 1)), TrailingMinusNumbers:=True

    

End Sub

and the code I am trying to import multiple files is:

Sub OpenManyFiles()

        Dim myFile As Variant
    Dim i As Long
    myFile = Application.GetOpenFilename("All Files,*.*", MultiSelect:=True)
    For i = LBound(myFile) To UBound(myFile)
    Workbooks.Open myFile(i)
    
        Workbooks.OpenText FileName:=myFile, _
        Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
        Array(Array(0, 1), Array(7, 1), Array(14, 1), Array(24, 1), Array(31, 1), Array(41, 1), _
        Array(53, 1), Array(62, 1), Array(68, 1), Array(78, 1), Array(86, 1), Array(93, 1), Array( _
        104, 1)), TrailingMinusNumbers:=True
        
        Next i
    
End Sub

hope that helps

**Ryan Murtagh** · 05-21-2010, 05:55 AM

Well, at first glance, I wonder if you need to use both the Workbooks.Open and the Workbooks.OpenText methods. Try it like this, with the Workbooks.Open method commented out.

NOTE: I also added the "(i)" to myFile, which was missing from your code. You need to have that index variable there when you loop through an array.

Sub OpenManyFiles()

    Dim myFile As Variant
    Dim i As Long
    myFile = Application.GetOpenFilename("All Files,*.*", MultiSelect:=True)
    For i = 1 To UBound(myFile)
    'Workbooks.Open myFile(i)
    
        Workbooks.OpenText FileName:=myFile(i), _
        Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
        Array(Array(0, 1), Array(7, 1), Array(14, 1), Array(24, 1), Array(31, 1), Array(41, 1), _
        Array(53, 1), Array(62, 1), Array(68, 1), Array(78, 1), Array(86, 1), Array(93, 1), Array( _
        104, 1)), TrailingMinusNumbers:=True
        
        Next i
    
End Sub

**sport_logo** · 05-21-2010, 07:04 AM

thats working now thanks very much for all you help

**Ryan Murtagh** · 05-21-2010, 07:34 AM

Outstanding! Happy to help...thanks for following up.

Problem with importing multiple data files with fixed width format

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Problem with importing multiple data files with fixed width format

Re: Problem with importing multiple data files with fixed width format

Re: Problem with importing multiple data files with fixed width format

Re: Problem with importing multiple data files with fixed width format

Re: Problem with importing multiple data files with fixed width format

Re: Problem with importing multiple data files with fixed width format

Re: Problem with importing multiple data files with fixed width format

Re: Problem with importing multiple data files with fixed width format

Re: Problem with importing multiple data files with fixed width format

Re: Problem with importing multiple data files with fixed width format

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions