How do I import several csv files into one excel worksheet?

chieftrain · 05-26-2006, 04:40 PM

Dear Madam, Sir,

if i want to import serveral csv files into one worksheet in Excel how will
the macro look like?

can you help me on that.

thanks in advance,

Rick Portier

**bgeier** · 05-26-2006, 05:28 PM

Unfortunatly, Excel will not import .csv files into one workbook.
You will have to import each one into its own workbook, then move the data from the file created by the import to another workbook, then delete the workbook that was used to import the data.

I have tried several times to get around this, and this seems to be the easiest.
The only other way is to use either input or line input (I cannot remember which I used), that way, you can. It can be tricky to do but is possible.

Ron de Bruin · 05-26-2006, 06:10 PM

See
http://www.rondebruin.nl/csv.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl

"chieftrain" <[email protected]> wrote in message news:[email protected]...
> Dear Madam, Sir,
>
> if i want to import serveral csv files into one worksheet in Excel how will
> the macro look like?
>
> can you help me on that.
>
> thanks in advance,
>
> Rick Portier

**marksince1984** · 06-03-2006, 03:59 AM

G'Day Ron,

Had a look at your work, but need it to import all tab-seperated-values. Can you give me a heads-up on what i need to change in your coding to make this work??

Thanks

Mark

' Start Code

 

Declare Function OpenProcess Lib "kernel32" _
        (ByVal dwDesiredAccess As Long, _
        ByVal bInheritHandle As Long, _
        ByVal dwProcessId As Long) As Long
 

Declare Function GetExitCodeProcess Lib "kernel32" _
        (ByVal hProcess As Long, _
        lpExitCode As Long) As Long
 

Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103
 


Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
    Dim hProg As Long
    Dim hProcess As Long, ExitCode As Long
    'fill in the missing parameter and execute the program
    If IsMissing(WindowState) Then WindowState = 1
    hProg = Shell(PathName, WindowState)
    'hProg is a "process ID under Win32. To get the process handle:
    hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
    Do
        'populate Exitcode variable
        GetExitCodeProcess hProcess, ExitCode
        DoEvents
    Loop While ExitCode = STILL_ACTIVE
End Sub

 

 

Sub Merge_CSV_Files()
    Dim BatFileName As String
    Dim TXTFileName As String
    Dim XLSFileName As String
    Dim DefPath As String
    Dim Wb As Workbook
    Dim oApp As Object
    Dim oFolder
    Dim foldername

 

    'Create two temporary file names
    BatFileName = Environ("Temp") & "\CollectCSVData" & Format(Now, "dd-mm-yy-h-mm-ss") & ".bat"
    TXTFileName = Environ("Temp") & "\AllCSV" & Format(Now, "dd-mm-yy-h-mm-ss") & ".txt"

 

    ' Create path to xls file
    DefPath = Application.DefaultFilePath
    If Right(DefPath, 1) <> "\" Then
        DefPath = DefPath & "\"
    End If
    XLSFileName = DefPath & "MasterCSV " & Format(Now, "dd-mm-yy h-mm-ss") & ".xls"

 

    'Browse to the folder with CSV files
    Set oApp = CreateObject("Shell.Application")
    Set oFolder = oApp.BrowseForFolder(0, "Select folder with CSV files", 512)
    If Not oFolder Is Nothing Then
        foldername = oFolder.Self.Path
        If Right(foldername, 1) <> "\" Then
            foldername = foldername & "\"
        End If

 

        'Create the bat file
        Open BatFileName For Output As #1
        Print #1, "Copy " & Chr(34) & foldername & "*.csv" & Chr(34) & " " & TXTFileName
        Close #1

 

        'Run the Bat file to collect all data from the CSV files into a TXT file
        ShellAndWait BatFileName, 0

        If Dir(TXTFileName) = "" Then
            MsgBox "There are no csv files in this folder"
            Kill BatFileName
            Exit Sub
        End If

 

        'Open the TXT file in Excel
        Application.ScreenUpdating = False
        Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows, StartRow _
                :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
                ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, _
                Space:=False, Other:=False

 

        'Save text file as a XLS file
        Set Wb = ActiveWorkbook
        Application.DisplayAlerts = False
        Wb.SaveAs Filename:=XLSFileName, FileFormat:=xlWorkbookNormal
        Application.DisplayAlerts = True

 

        Wb.Close savechanges:=False
        MsgBox "You find the XLS file here: " & XLSFileName
        

        'Delete the bat and text file you have create
        Kill BatFileName
        Kill TXTFileName

 

        Application.ScreenUpdating = True
    End If
End Sub

' End code

Dave Peterson · 06-03-2006, 08:45 AM

Without any testing...

'Open the TXT file in Excel
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False

becomes:

'Open the TXT file in Excel
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=TRUE, Semicolon:=False, Comma:=FALSE, _
Space:=False, Other:=False

marksince1984 wrote:
>
> G'Day Ron,
>
> Had a look at your work, but need it to import all
> tab-seperated-values. Can you give me a heads-up on what i need to
> change in your coding to make this work??
>
> Thanks
>
> Mark
>
> Code:
> --------------------
> ' Start Code
>
>
>
> Declare Function OpenProcess Lib "kernel32" _
> (ByVal dwDesiredAccess As Long, _
> ByVal bInheritHandle As Long, _
> ByVal dwProcessId As Long) As Long
>
>
> Declare Function GetExitCodeProcess Lib "kernel32" _
> (ByVal hProcess As Long, _
> lpExitCode As Long) As Long
>
>
> Public Const PROCESS_QUERY_INFORMATION = &H400
> Public Const STILL_ACTIVE = &H103
>
>
>
> Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
> Dim hProg As Long
> Dim hProcess As Long, ExitCode As Long
> 'fill in the missing parameter and execute the program
> If IsMissing(WindowState) Then WindowState = 1
> hProg = Shell(PathName, WindowState)
> 'hProg is a "process ID under Win32. To get the process handle:
> hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
> Do
> 'populate Exitcode variable
> GetExitCodeProcess hProcess, ExitCode
> DoEvents
> Loop While ExitCode = STILL_ACTIVE
> End Sub
>
>
>
>
>
> Sub Merge_CSV_Files()
> Dim BatFileName As String
> Dim TXTFileName As String
> Dim XLSFileName As String
> Dim DefPath As String
> Dim Wb As Workbook
> Dim oApp As Object
> Dim oFolder
> Dim foldername
>
>
>
> 'Create two temporary file names
> BatFileName = Environ("Temp") & "\CollectCSVData" & Format(Now, "dd-mm-yy-h-mm-ss") & ".bat"
> TXTFileName = Environ("Temp") & "\AllCSV" & Format(Now, "dd-mm-yy-h-mm-ss") & ".txt"
>
>
>
> ' Create path to xls file
> DefPath = Application.DefaultFilePath
> If Right(DefPath, 1) <> "\" Then
> DefPath = DefPath & "\"
> End If
> XLSFileName = DefPath & "MasterCSV " & Format(Now, "dd-mm-yy h-mm-ss") & ".xls"
>
>
>
> 'Browse to the folder with CSV files
> Set oApp = CreateObject("Shell.Application")
> Set oFolder = oApp.BrowseForFolder(0, "Select folder with CSV files", 512)
> If Not oFolder Is Nothing Then
> foldername = oFolder.Self.Path
> If Right(foldername, 1) <> "\" Then
> foldername = foldername & "\"
> End If
>
>
>
> 'Create the bat file
> Open BatFileName For Output As #1
> Print #1, "Copy " & Chr(34) & foldername & "*.csv" & Chr(34) & " " & TXTFileName
> Close #1
>
>
>
> 'Run the Bat file to collect all data from the CSV files into a TXT file
> ShellAndWait BatFileName, 0
>
> If Dir(TXTFileName) = "" Then
> MsgBox "There are no csv files in this folder"
> Kill BatFileName
> Exit Sub
> End If
>
>
>
> 'Open the TXT file in Excel
> Application.ScreenUpdating = False
> Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows, StartRow _
> :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
> ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, _
> Space:=False, Other:=False
>
>
>
> 'Save text file as a XLS file
> Set Wb = ActiveWorkbook
> Application.DisplayAlerts = False
> Wb.SaveAs Filename:=XLSFileName, FileFormat:=xlWorkbookNormal
> Application.DisplayAlerts = True
>
>
>
> Wb.Close savechanges:=False
> MsgBox "You find the XLS file here: " & XLSFileName
>
>
> 'Delete the bat and text file you have create
> Kill BatFileName
> Kill TXTFileName
>
>
>
> Application.ScreenUpdating = True
> End If
> End Sub
>
> ' End code
> --------------------
>
> --
> marksince1984
> ------------------------------------------------------------------------
> marksince1984's Profile: http://www.excelforum.com/member.php...o&userid=33484
> View this thread: http://www.excelforum.com/showthread...hreadid=546042

--

Dave Peterson

Ron de Bruin · 06-03-2006, 09:35 AM

For the OP there is a Note about this on the page
http://www.rondebruin.nl/csv.htm

--
Regards Ron De Bruin
http://www.rondebruin.nl

"Dave Peterson" <[email protected]> wrote in message news:[email protected]...
> Without any testing...
>
> 'Open the TXT file in Excel
> Application.ScreenUpdating = False
> Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows, StartRow _
> :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
> ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, _
> Space:=False, Other:=False
>
>
> becomes:
>
> 'Open the TXT file in Excel
> Application.ScreenUpdating = False
> Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows, StartRow _
> :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
> ConsecutiveDelimiter:=False, Tab:=TRUE, Semicolon:=False, Comma:=FALSE, _
> Space:=False, Other:=False
>
>
>
>
> marksince1984 wrote:
>>
>> G'Day Ron,
>>
>> Had a look at your work, but need it to import all
>> tab-seperated-values. Can you give me a heads-up on what i need to
>> change in your coding to make this work??
>>
>> Thanks
>>
>> Mark
>>
>> Code:
>> --------------------
>> ' Start Code
>>
>>
>>
>> Declare Function OpenProcess Lib "kernel32" _
>> (ByVal dwDesiredAccess As Long, _
>> ByVal bInheritHandle As Long, _
>> ByVal dwProcessId As Long) As Long
>>
>>
>> Declare Function GetExitCodeProcess Lib "kernel32" _
>> (ByVal hProcess As Long, _
>> lpExitCode As Long) As Long
>>
>>
>> Public Const PROCESS_QUERY_INFORMATION = &H400
>> Public Const STILL_ACTIVE = &H103
>>
>>
>>
>> Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
>> Dim hProg As Long
>> Dim hProcess As Long, ExitCode As Long
>> 'fill in the missing parameter and execute the program
>> If IsMissing(WindowState) Then WindowState = 1
>> hProg = Shell(PathName, WindowState)
>> 'hProg is a "process ID under Win32. To get the process handle:
>> hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
>> Do
>> 'populate Exitcode variable
>> GetExitCodeProcess hProcess, ExitCode
>> DoEvents
>> Loop While ExitCode = STILL_ACTIVE
>> End Sub
>>
>>
>>
>>
>>
>> Sub Merge_CSV_Files()
>> Dim BatFileName As String
>> Dim TXTFileName As String
>> Dim XLSFileName As String
>> Dim DefPath As String
>> Dim Wb As Workbook
>> Dim oApp As Object
>> Dim oFolder
>> Dim foldername
>>
>>
>>
>> 'Create two temporary file names
>> BatFileName = Environ("Temp") & "\CollectCSVData" & Format(Now, "dd-mm-yy-h-mm-ss") & ".bat"
>> TXTFileName = Environ("Temp") & "\AllCSV" & Format(Now, "dd-mm-yy-h-mm-ss") & ".txt"
>>
>>
>>
>> ' Create path to xls file
>> DefPath = Application.DefaultFilePath
>> If Right(DefPath, 1) <> "\" Then
>> DefPath = DefPath & "\"
>> End If
>> XLSFileName = DefPath & "MasterCSV " & Format(Now, "dd-mm-yy h-mm-ss") & ".xls"
>>
>>
>>
>> 'Browse to the folder with CSV files
>> Set oApp = CreateObject("Shell.Application")
>> Set oFolder = oApp.BrowseForFolder(0, "Select folder with CSV files", 512)
>> If Not oFolder Is Nothing Then
>> foldername = oFolder.Self.Path
>> If Right(foldername, 1) <> "\" Then
>> foldername = foldername & "\"
>> End If
>>
>>
>>
>> 'Create the bat file
>> Open BatFileName For Output As #1
>> Print #1, "Copy " & Chr(34) & foldername & "*.csv" & Chr(34) & " " & TXTFileName
>> Close #1
>>
>>
>>
>> 'Run the Bat file to collect all data from the CSV files into a TXT file
>> ShellAndWait BatFileName, 0
>>
>> If Dir(TXTFileName) = "" Then
>> MsgBox "There are no csv files in this folder"
>> Kill BatFileName
>> Exit Sub
>> End If
>>
>>
>>
>> 'Open the TXT file in Excel
>> Application.ScreenUpdating = False
>> Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows, StartRow _
>> :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
>> ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, _
>> Space:=False, Other:=False
>>
>>
>>
>> 'Save text file as a XLS file
>> Set Wb = ActiveWorkbook
>> Application.DisplayAlerts = False
>> Wb.SaveAs Filename:=XLSFileName, FileFormat:=xlWorkbookNormal
>> Application.DisplayAlerts = True
>>
>>
>>
>> Wb.Close savechanges:=False
>> MsgBox "You find the XLS file here: " & XLSFileName
>>
>>
>> 'Delete the bat and text file you have create
>> Kill BatFileName
>> Kill TXTFileName
>>
>>
>>
>> Application.ScreenUpdating = True
>> End If
>> End Sub
>>
>> ' End code
>> --------------------
>>
>> --
>> marksince1984
>> ------------------------------------------------------------------------
>> marksince1984's Profile: http://www.excelforum.com/member.php...o&userid=33484
>> View this thread: http://www.excelforum.com/showthread...hreadid=546042
>
> --
>
> Dave Peterson

**marksince1984** · 06-03-2006, 10:11 PM

There is a check in there that the folder contains csv files, it is this i need to get around. Any ideas?

Originally Posted by Dave Peterson

Without any testing...

'Open the TXT file in Excel
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False

becomes:

'Open the TXT file in Excel
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=TRUE, Semicolon:=False, Comma:=FALSE, _
Space:=False, Other:=False

Dave Peterson · 06-04-2006, 08:25 AM

If you're merging CSV files and there are no CSV files in that folder, why would
you want to get around that check?

marksince1984 wrote:
>
> There is a check in there that the folder contains csv files, it is this
> i need to get around. Any ideas?
>
> Dave Peterson Wrote:
> > Without any testing...
> >
> > 'Open the TXT file in Excel
> > Application.ScreenUpdating = False
> > Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows, StartRow
> > _
> > :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
> > ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True,
> > _
> > Space:=False, Other:=False
> >
> >
> > becomes:
> >
> > 'Open the TXT file in Excel
> > Application.ScreenUpdating = False
> > Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows, StartRow
> > _
> > :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
> > ConsecutiveDelimiter:=False, Tab:=TRUE, Semicolon:=False, Comma:=FALSE,
> > _
> > Space:=False, Other:=False
> >
> >
> >
>
> --
> marksince1984
> ------------------------------------------------------------------------
> marksince1984's Profile: http://www.excelforum.com/member.php...o&userid=33484
> View this thread: http://www.excelforum.com/showthread...hreadid=546042

--

Dave Peterson

**marksince1984** · 06-06-2006, 03:18 AM

Originally Posted by marksince1984

G'Day Ron,

Had a look at your work, but need it to import all tab-seperated-values. Can you give me a heads-up on what i need to change in your coding to make this work??

Thanks

Mark

So what good is looking for csv files??