can I Import Multiple Fixed Width Files at once

[kat50]

Is it possible to import multiple fixed with files at once? I can make the following work, but I do not know how to allow the user to search for a file.


Sub OpenTextFile()

Const xlFixedWidth = 2
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.OpenText _
"C:\Users\Kate\Desktop\Test.txt", , , xlFixedWidth, , , , , , , , , Array(Array(0, 1), Array(4, 1), Array(9, 1), Array(23, 1), Array(30, 1), Array(33, 1), Array(42, 1), Array(48, 1), Array(51, 1), Array(54, 1), Array(64, 1), Array(70, 1), Array(80, 1))

End Sub

Thank you so much!

 

[Norie]

You could use Application.GetFileName to allow the user to select a file/files.

If you do want to import multiple files then there is no way to do it 'at once'.

You would need to loop through all the files and import them as required.

By the way where are you running this code from, is it Excel?

If it is why are you creating another instance of Excel here?

Set objExcel = CreateObject("Excel.Application")

 

[kat50]

Thanks for the quick reply.

I am doing this in excel. I was trying to mimic an example I found on the Tech Net site...

if I use: Application.GetFileName can you tell me where I should stick that in the module?

Thanks so much!

 

[Norie]

Not quite sure since I don't know exactly what you want to do.

I suppose the obvious answer is you put it where you want the users to be able to select the file(s).

 

[kat50]

Sorry I am not that savvy. I would like the user to be able to browse to the file that is being imported. This module parses the data as needed, but I have hard keyed the file name. Can you advise on correcting that?

I really appreciate your help!