tiredofit
Well-known Member
- Joined
- Apr 11, 2013
- Messages
- 1,834
- Office Version
- 365
- 2019
- Platform
- Windows
I am trying to retrieve data from a csv file via ADO.
If I open the csv file, the data format for my numbers are just that, numbers.
However, once I run the following code, it turns these numbers into text.
Can someone please tell me how I can retrieve the data so that it returns numbers, not text?
Thanks
If I open the csv file, the data format for my numbers are just that, numbers.
However, once I run the following code, it turns these numbers into text.
Can someone please tell me how I can retrieve the data so that it returns numbers, not text?
Thanks
Code:
Option Explicit
Sub ExtractQuery()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Dim rng As Range
Dim strcon As String
Dim strSQL As String
Dim DBLoc As String
DBLoc = "C:\DataLocation\"
On Error GoTo ErrHandler
' The database connection string.
strcon = "Provider = Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & DBLoc & ";" & _
"Extended Properties=""text;" & _
"HDR=Yes;" & _
"FMT=Delimited"""
cn.Open ConnectionString:=strcon
' This is the range that will receive the data.
Set rng = Cells(2, 1)
' The query to execute
strSQL = "SELECT * FROM [Myfile.csv]"
' Create & Open the recordset
Set rs = New ADODB.Recordset
rs.Open Source:=strSQL, _
ActiveConnection:=strcon
' Copy to the range
rng.CopyFromRecordset Data:=rs
' Close the recordset when you're done with it.
rs.Close
cn.Close
Set rng = Nothing
Set rs = Nothing
Set cn = Nothing
Exit Sub
ErrHandler:
MsgBox Prompt:="Sorry, an error occured. " & Err.Description, _
Buttons:=vbOKOnly, _
Title:="Error retrieving data"
' resume at the ExitPoint label to clean up object variables
Set rng = Nothing
Set rs = Nothing
Set cn = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
End
End Sub
Last edited:
