Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > How do I import several csv files into one excel worksheet?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
chieftrain
 
Posts: n/a
Default How do I import several csv files into one excel worksheet?
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
bgeier
 
Posts: n/a
Default How do I import several csv files into one excel worksheet?

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.


--
bgeier
------------------------------------------------------------------------
bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
View this thread: http://www.excelforum.com/showthread...hreadid=546042

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
Ron de Bruin
 
Posts: n/a
Default How do I import several csv files into one excel worksheet?
See
http://www.rondebruin.nl/csv.htm


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


"chieftrain" wrote in message ...
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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
marksince1984
 
Posts: n/a
Default How do I import several csv files into one excel worksheet?

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 he " & 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default How do I import several csv files into one excel worksheet?
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 he " & 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
marksince1984
 
Posts: n/a
Default How do I import several csv files into one excel worksheet?

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

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default How do I import several csv files into one excel worksheet?
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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
Ron de Bruin
 
Posts: n/a
Default How do I import several csv files into one excel worksheet?
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" wrote in message ...
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 he " & 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


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.misc
marksince1984
 
Posts: n/a
Default How do I import several csv files into one excel worksheet?

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


read my first post in this thread - it seems to be pretty
self-explanatory


--
marksince1984
------------------------------------------------------------------------
marksince1984's Profile: http://www.excelforum.com/member.php...o&userid=33484
View this thread: http://www.excelforum.com/showthread...hreadid=546042

Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default How do I import several csv files into one excel worksheet?
It still isn't to me.

Good luck.

marksince1984 wrote:

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


read my first post in this thread - it seems to be pretty
self-explanatory

--
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
Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.misc
marksince1984
 
Posts: n/a
Default How do I import several csv files into one excel worksheet?

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


So what good is looking for csv files??


--
marksince1984
------------------------------------------------------------------------
marksince1984's Profile: http://www.excelforum.com/member.php...o&userid=33484
View this thread: http://www.excelforum.com/showthread...hreadid=546042

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't access excel files from worksheet Craig Excel Discussion (Misc queries) 2 April 24th 06 09:59 PM
HELP: Import several TXT files into Excel luis Excel Discussion (Misc queries) 6 April 9th 06 09:16 PM
Sharing read-write Excel 2003 files ttt8262 Excel Discussion (Misc queries) 0 April 1st 06 09:39 PM
import excel worksheet info to contact sub-folder mary Excel Discussion (Misc queries) 1 January 14th 06 05:51 PM
Can I import a windows explorer list of files into an Excel sprea. skeliher Excel Discussion (Misc queries) 2 December 1st 04 10:24 PM


All times are GMT +1. The time now is 04:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement