Atravis
08-13-2010, 08:54 AM
Hi,
I am trying to import the data from closed CSV files in an excel spreadsheet using an ADO connection. If there are positive and negative numbers in the columns being imported the negative values are missing.
I need these numbers to be included, and any help on this would be appreciated. Please find the code below.
'Pulls Data from CSV to Data sheet
Sub Open_Sort_CSV(CSV_Dir, CSV_name)
'set record set variables
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
'ser connection and recordset
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
'oopen connection (headers,Delimited style,mixed data taken as text(not sure imex works))
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CSV_Dir & ";" & _
"Extended Properties=""TEXT;HDR=Yes;FMT=Delimited;IMEX=1"""
'get data from csv
objRecordset.Open "SELECT * FROM " & CSV_name, _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
'Loop across the fields
With objRecordset
For a& = 0 To .Fields.Count - 1
' Add field names to data sheet
ThisWorkbook.Worksheets("Data").Cells(1, 1).Offset(0, a&).Value = .Fields(a&).Name
Next a&
End With
'this errors for no good reason so stop any errors
On Error Resume Next
'copy data into worksheet under headers
ThisWorkbook.Worksheets("Data").Cells(2, 1).CopyFromRecordset objRecordset
'end connection and recordset
Set objConnection = Nothing
Set objRecordset = Nothing
End Sub
I am trying to import the data from closed CSV files in an excel spreadsheet using an ADO connection. If there are positive and negative numbers in the columns being imported the negative values are missing.
I need these numbers to be included, and any help on this would be appreciated. Please find the code below.
'Pulls Data from CSV to Data sheet
Sub Open_Sort_CSV(CSV_Dir, CSV_name)
'set record set variables
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
'ser connection and recordset
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
'oopen connection (headers,Delimited style,mixed data taken as text(not sure imex works))
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CSV_Dir & ";" & _
"Extended Properties=""TEXT;HDR=Yes;FMT=Delimited;IMEX=1"""
'get data from csv
objRecordset.Open "SELECT * FROM " & CSV_name, _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
'Loop across the fields
With objRecordset
For a& = 0 To .Fields.Count - 1
' Add field names to data sheet
ThisWorkbook.Worksheets("Data").Cells(1, 1).Offset(0, a&).Value = .Fields(a&).Name
Next a&
End With
'this errors for no good reason so stop any errors
On Error Resume Next
'copy data into worksheet under headers
ThisWorkbook.Worksheets("Data").Cells(2, 1).CopyFromRecordset objRecordset
'end connection and recordset
Set objConnection = Nothing
Set objRecordset = Nothing
End Sub