Software

Ask something
Discussion Locked

auto import multiple files into excel

0 helpful
8 replies

by seadogs

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I am trying to import a couple hundred .csv files into one excel 2000 worksheet. I'd like to be able to do it automatically, but know virtually nothing about this. Basically each file has a different name, ex a-12.csv, b-13.csv. They are all in the same directory. What I'd like to do is put the file name (minus .csv if possible, but not necessary) in the B column for each line within the file that I'm importing. That way I know which data came from which file. The data starts in the c column. The data is comma deliminated, and when I import manually, all I have to do is click on the cell I want the data to start in, look for a .csv file, open it, set comma deliters and import. Is there anyway to do this? I'm pretty clueless so even if there isn't if there are any suggestions on just making the process easier I'd appreciate it!

8 replies

by seadogs

answer 1 is a great help! That is certainly simplifying things. I can also see what the script is diong. That leaves 2 more steps:
1. Is there anyway to import just the name of a file into a cell in excel?
2. Is there a way to repeat this process until I opt out w/out having to run the macro over and over? for now I'll put a shortcut key to it I think.

by seadogs

is there a wait command I can put into the script to cause it to wait for approx 5 seconds before proceeding? Thanks!

by nikki96

How's this?
Substitute your directory where the files are for my "my documents" folder.

Tested in Excel 2000 with a directory of CSV files only. Function assumes active cell is NOT in column A. Function assumes all files in directory are CSV files.
Help taken from previously mentioned site and added / modified code.

HTH
Nikki

Public Sub ImportTextFile(fname As String, sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
DimPos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open fname For Input Access Read As #1
Cells(RowNdx, SaveColNdx - 1).Value = fname
While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> sep Then
WholeLine = WholeLine & sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, sep)
Wend
RowNdx = RowNdx + 1
Wend
Cells(RowNdx, SaveColNdx).Select
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

Sub getFiles()
Dim fs, f, f1, fc, s, a
Dim folderspec, sep As String
sep = ","
folderspec = "C:\Documents and Settings\hunn\My Documents\temp\testDir"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
For Each f1 In fc
a = MsgBox("Import next file?", vbYesNo) If a = vbNo Then
Exit Sub
End If
s = s & f1.Name
s = s & vbCrLf
ImportTextFile CStr(f1), sep
Next
MsgBox s
End Sub

by nikki96

If you wanted the timer instead of asking, change the function to this.

Also, I forgot to mention I left in the MSGBOX debug that lists the filenames. You can take that out.

Nikki

Sub getFiles()
Dim fs, f, f1, fc, s, a
Dim PauseTime, Start
Dim folderspec, sep As String
sep = ","
folderspec = "C:\Documents and Settings\hunn\My Documents\temp\testDir"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
For Each f1 In fc
Application.ScreenUpdating = True
PauseTime = 5 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes. Loop
Finish = Timer ' Set end time.
Application.ScreenUpdating = False
s = s & f1.Name
s = s & vbCrLf
ImportTextFile CStr(f1), sep
Next
MsgBox s
End Sub

by seadogs

I tried answer 2 and get an error with a 400 code, nothing else. Is it possible there's a typo in there? I'm a little lost in following that code, and I don't see anything wrong myself. There aren't any errors when I put it in excel, just when I run it. On the other hand, I've got it running using answer #1, I added a delete file procedure so I just pick the top file in the directory each time. I still have to select the macro to run and put the file name in column b for each entry. Any ideas on those two points?

by seadogs

I tried answer 2 and get an error with a 400 code, nothing else. Is it possible there's a typo in there? I'm a little lost in following that code, and I don't see anything wrong myself. There aren't any errors when I put it in excel, just when I run it. On the other hand, I've got it running using answer #1, I added a delete file procedure so I just pick the top file in the directory each time. I still have to select the macro to run and put the file name in column b for each entry. Any ideas on those two points?

This discussion is closed to new replies.