I can not read data that I have written into Excel from VB, back into VB after it is
edited in Excel by the user. It appears that the numeric data is going into
Excel as a text cell. It is a CHAR 7 defined field in mySQL. When the cell
is edited in Excel, the format changes to numeric and when I read the data
back into Excel, the value of the cell is "". If there is no user editing
in Excel, and the cell retains it's text formatting, I can read it back
into VB with no problem.
Also, when the data is first opened in Excel, the number is left justified
in the cell, as text should be. Upon editing the cell, the number becomes
right justified, as a number should be, and the import back into VB fails.
If I change the properties of the cell from numeric to text, the import
picks up the edited data.
If the user enters the data like this '123 (with a leading single quote)
then Excel treats it as text and the VB import works. Also, if the user
enters V123 it imports fine too.
I am using Excel 97 to edit the data and I can not have my users entering
numerics with the leading single quote.
My code to read from Excel is:
Dim csql As String
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; " + _
"Data Source=c:\eSheet.xls;Extended Properties=Excel 5.0;"
cnn.Open
Dim adoPrimaryRS As Recordset
Set adoPrimaryRS = New Recordset
csql = "SELECT [Sheet1$].* From [Sheet1$]"
adoPrimaryRS.Open csql, cnn, adOpenStatic, adLockOptimistic
My code to write to Excel is:
Dim strSQL As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = "Sheet1"
'Insert column headings
xlSheet.Cells(1, 1).Value = "Range Start"
xlSheet.Cells(i, 1).Value = rsData("Method Code"
xlSheet.SaveAs "c:\eSheet.xls"
xlApp.Quit
Can anyone see my error or point me in the right direction?
Thank you VERY much.
