Help to import multiple text files to worksheets in a excel workbook

**cookiemonster79** · 08-22-2011, 10:10 AM

Hi

I have no programming background but have managed to record a macro to import a single text file into a worksheet.

The code is below but

    Workbooks.OpenText FileName:="W:\xyz\abc\output.txt.server1", Origin _
        :=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0 _
        , 1), Array(9, 1), Array(15, 1), Array(22, 1), Array(23, 1), Array(29, 1), Array(35, 1), _
        Array(47, 1)), TrailingMinusNumbers:=True

Can someone show me how to amend this so that the macro does a loop and picks up all the text files under location W:\xyz\abc\

I did try code like below but i didnt work

 Do While FileName <> ""
        Workbooks.OpenText FileName:="W:\xyz\abc\" & strFileNameHere, _

Also when the save is done to the worksheet, I would like each worksheet within the spreadsheet to be called the name of the text file that has been run in? can someone please provide the code on how to do this?

Thanks

**tarquinious** · 08-24-2011, 06:18 AM

I have written something which may help. The code has comments to explain what it is doing. Let me know if any changes are required, or if you're happy to take it from there.

**cookiemonster79** · 08-26-2011, 12:02 PM

This is great thank you, there is only thing i have not been able to get working. Currently the files I importing are the process output of servers (ps- aef on linux).

At the moment the rows of data in my file are all imported into one column like
Column A
root 2168 1 0 Aug07 ? 00:00:03 /usr/sbin/automount --timeout=60 /apps yp auto.apps
root 2463 1 0 Aug07 ? 00:00:02 /usr/sbin/automount --timeout=60 /data yp auto.data

but i actually want to split this info as per the tabs in the file across several columns as per the tabs in the text file so i can filter columns more easily.

I got the code from the macro when it imports the file with fixed width columns via an external import and added it to the code but it still will not import the data across several columns. I havent been able to understand why this is.

The code i placed in was

.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(8, 6, 8, 1, 6, 6, 13)

**jrabbit** · 09-23-2011, 03:48 PM

Originally Posted by tarquinious

I have written something which may help. The code has comments to explain what it is doing. Let me know if any changes are required, or if you're happy to take it from there.

Hi Clever Excel people,

I've tried to use this macro but nowt happens when I press the button (I'm assuming you have to press the 'import text Files' Button). I think maybe because I am using excel for mac 2011. Can I modify anything to make it work for me?

Many thanks.

sarah

**sriganesh.ravi** · 10-31-2011, 02:17 PM

hi..i am trying to import multiple .ldif files into excel..the plan is to eventually upload this onto the access database..but i feel it would be easier to upload this onto excel first..so the file is of the format

dn: radiusname=KEVIN,radiusclass=Native-User,o=radius

changetype: add

objectclass: top

objectclass: Native-User

objectclass: user

radiusname: KEVIN

password: {x-clear}secret1

profile: ISDN

login-limit: 2

with the field before the : as column header and the field after the : as the value..there are 19000 separate .ldif files that i need to import..i tried running a macro which imports the values as to separate columns and then applied a transpose on the same..i did this via a macro..but this was just for 1 file..i need to to this with 19000 files..so if anyone is familiar with multiple .ldif import to excel please let me know...quick replies would be appreciated as this is kind of urgent..