Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hi, I have the following code below which happily breaks down a CSV
into different worksheets in excel. However, what I want the VBA code to do is copy the recordset to new CSV files, my code already creates the new CSV files, but how do I send the data to the csv file and not the worksheet? Thanks Code: Option Explicit Sub ImportLargeFile() Dim strFilePath As String, strFilename As String, strFullPath As String Dim oFile As TextStream Dim lngCounter As Long Dim oConn As Object, oRS As Object, oFSObj As Object Dim xFile As Integer Dim xFileName As String Dim fso As FileSystemObject Set fso = New FileSystemObject Dim xStr As String xFile = 1 xFileName = "C:\Users\Steve\Desktop\New folder\output" 'Get a text file name strFullPath = "C:\Users\Steve\Desktop\New folder\test.csv" 'This gives us a full path name e.g. C:tempfolderfile.txt 'We need to split this into path and file name Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT") strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.Path strFilename = oFSObj.GetFile(strFullPath).Name Debug.Print (strFilePath) Debug.Print (strFilename) 'Open an ADO connection to the folder specified Set oConn = CreateObject("ADODB.CONNECTION") oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strFilePath & ";" & _ "Extended Properties=""text;HDR=Yes;FMT=Delimited""" Set oRS = CreateObject("ADODB.RECORDSET") 'Now actually open the text file and import into Excel oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1 While Not oRS.EOF xFileName = "C:\Users\Steve\Desktop\New folder\output" & xFile & ".csv" Set oFile = fso.CreateTextFile(xFileName, True) Worksheets(xFile).Range("A1").CopyFromRecordset oRS, 10000 xFile = xFile + 1 Wend oRS.Close oConn.Close Application.ScreenUpdating = True End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Cannot use recordset twice with copy From recordset | Excel Programming | |||
Copy from recordset errors in ADO | Excel Programming | |||
Copy an array into an ADO recordset? | Excel Programming | |||
Copy From Recordset | Excel Programming | |||
Copy Recordset into array? | Excel Programming |