Using VBA in Access to create ADODB Connection to Excel
Posted on 2006-07-03
While using VBA in Access 2003, I have created an ADODB connection to connect to a workbook in excel. The workbook may have several sheets in it, and each sheet seldom has above 250 rows. The ADODB connection is created individually for each sheet. I have a particular column("A") in the excel file that is supposed to be a text string. In some fields the string is just numbers, and Excel is noticing that and has the green triangle error message saying "Number Stored as text".
When I loop through the rows made in the ADODB connection some of the cells with the number stored as text are Null, and some values are correct. I have made some attempts to edit the spreadsheet so this error message isn't there, but it is only successful some of the time. I've read some other questions on here, but wasn't sure what would be best for my situation. I have attached my connection script below.
What is the best way to make these values import correctly 100% of the time?
My end result of this connection is to run an INSERT query to add these spreadsheet rows into the Access DB.
Thanks for your help. I will happy to expound more if needed.
'variable b is the name of the sheet
Dim rs2 As New ADODB.Recordset
Dim cnn2 As New ADODB.Connection
Dim cmd2 As New ADODB.Command
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & fileName & "; Extended Properties=Excel 8.0;"
Set cmd2.ActiveConnection = cnn2
cmd2.CommandType = adCmdText
cmd2.CommandText = "SELECT * FROM [" & b & "$]"
rs2.CursorLocation = adUseClient
rs2.CursorType = adOpenStatic
rs2.LockType = adLockReadOnly
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset