Appending txt file to an existing spreadsheet (2000 SR1)

[bewens]

<img src=/S/help.gif border=0 alt=help width=23 height=15> I'm not even sure if it is possible to automate this, but it would save me a LOT of time if it is. I have a bunch (about 700) of comma delimited text files that I would like to import into one spreadsheet. I have two problems with this.

1. I want to automate the whole Text Import Wizard so I don't have to go through the the wizard for each one

2. I don't know how to "append" one spreadsheet to the end of another without copying and pasting the cells over (very tedious)

If anyone has any ideas as to how to make this process a little easier, I'd love some suggestions.

Thanks,
Becky

[LegareColeman]

It should be possible to write a macro that will do most if not all of what you want automatically. However, we need a bit more information:

1- What Wizard are you using to import these files? If they are .CSV files, can't you just open them?

2- Is the format of all of the files identical? Can each file just be opened and the data copied and pasted at the end of of the combined sheet?

3- Are there any header rows in each file that must not be copied after the first file?

4- How can the files be found and recognized? Is it something like all of the files with a .CSV extension in a particular directory?

5- Can you attach a dummy copy of one of the files?

[bewens]

To answer your questions:

1. I've just been using the wizard that automatically pops up in Excel whenver you open a delimited .txt file.

2. The format is exactly identical-the server pops one of these reports out each day, so copying and pasting the info would work just fine.

3. There are no header rows to worry about

4. All the files are in one particular directory-I've moved them specifically to D:logfiles There is nothing else in that folder except for those text files.

5. I have attached one of the files. The ultimate goal is to calculate the total amount of time (Column D) each person (Column [img]/forums/images/smilies/cool.gif[/img] has spent accessing the particular server.

Thanks,
Becky

[LegareColeman]

OK, those are tab delimited files not comma delimited and the import wizard does pop up when you try to open one. I decided that it was easier to just write a macro to import the files rather than try to figure out how to control the wizard from a macro. The following macro should import all of the .log files in directory D:logfiles into Sheet1 of the active workbook when it is run.

<pre>Public Sub LoadFiles()
Dim strFileName As String, strPath As String, strLine As String
Dim I As Long, J As Long, iFileNum As Integer, iLen As Integer
strPath = "D:logfiles"
I = 0
iFileNum = FreeFile
strFileName = Dir(strPath & "*.log")
With Worksheets("Sheet1").Range("A1")
Do While strFileName <> ""
Open strPath & strFileName For Input As #iFileNum
Do While Not EOF(iFileNum)
Input #iFileNum, strLine
strLine = Trim(strLine)
J = 0
Do While Len(strLine) > 0
iLen = InStr(strLine, Chr(9))
.Offset(I, J).Value = Trim(Left(strLine, iLen - 1))
strLine = Trim(Right(strLine, Len(strLine) - iLen))
J = J + 1
Loop
I = I + 1
Loop
Close #iFileNum
strFileName = Dir
Loop
End With
End Sub
</pre>

[bewens]

I'm sorry, the files actually are comma delimited, but I had to open it up and remove some confidential info. When I saved it back again, I didn't pay attention to the save as type.

I've tried running the macro several times, but it has an error on the
.Offset(I, J).Value = Trim(Left(strLine, iLen - 1))
line. When I removed that line, it seemed to get caught in a loop, so I couldn't get it to work right. Any suggestions?

[LegareColeman]

It's a little tough writing the code without the actual file to test with. I took a guess at what the file looks like. Try this:

<pre>Public Sub LoadFiles()
Dim strFileName As String, strPath As String, strLine As String
Dim I As Long, J As Long, iFileNum As Integer, iLen As Integer
strPath = "D:logfiles"
I = 0
iFileNum = FreeFile
strFileName = Dir(strPath & "*.log")
With Worksheets("Sheet1").Range("A1")
Do While strFileName <> ""
Open strPath & strFileName For Input As #iFileNum
Do While Not EOF(iFileNum)
Line Input #iFileNum, strLine
strLine = Trim(strLine) & ","
J = 0
Do While Len(strLine) > 0
iLen = InStr(strLine, ",")
.Offset(I, J).Value = Trim(Left(strLine, iLen - 1))
strLine = Trim(Right(strLine, Len(strLine) - iLen))
J = J + 1
Loop
I = I + 1
Loop
Close #iFileNum
strFileName = Dir
Loop
End With
End Sub
</pre>

[bewens]

I really appreciate all the work you've done on this for me. I'm attaching another file that I opened it in Word to replaced all confidential info, so hopefully this file will be accurate. I had another problem with this bit of code. I got an invalid syntax error on the
Do While strFileName <> ""
and
Do While Len(strLine) > 0
lines. I don't know what it's supposed to be doing here, so I couldn't even begin to fix it. If you want to take another stab at it (for fun), that would be great. If you don't, that's fine, too. I can just tell my boss that it can't be done <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.

Thanks again,
Becky

[LegareColeman]

I downloaded this file and then copied the code from my previous message and pasted it into a new workbook. The only change I made to the code was to change the D: drive in the path to the log files to C: since I do not have a D: drive on this computer. It ran with no problems on my Excel 97/SR2. I have attached the workbook showing the results and containing the macro (with the drive changed back to D<img src=/S/smile.gif border=0 alt=smile width=15 height=15>. Try running it from this workbook and see what happens. If it still gets the error, then my only guess is that something changed in Excel 2000, and I don't have access to 2000 where I am at the moment to try it. If you still have a problem, post another message, and maybe someone that does have 2000 can find the problem.

[bewens]

Legare,

Thanks so much. It works wonderfully!!! <img src=/S/joy.gif border=0 alt=joy width=23 height=23> I have no idea what I did wrong, but I'll look over my code v. your code and try to see what I can see.

Thanks again,

Becky
<img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>

[LegareColeman]

Just in case there was a change in XL 2000 on the Do While statement, I have attached another workbook that replaces those with While Wend, which I think is now the preferred construct.