October 25, 2005 at 12:15 pm
I have something set up to create linked servers to MS Excel files and read the spreadsheet values into a table. Now I'm trying to do the same thing, except I want to read in the MS Excel cell formulas rather than the values. Does anyone have any idea how to do this? Thanks
October 26, 2005 at 3:02 pm
Hi,
I had the same problem and one way to solve it is like:
Dim cnx As ADODB.Connection
Set cnx = New ADODB.Connection
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cnx.Provider = "sqloledb"
cnx.Properties("Network Library").Value = "DBMSSOCN" 'Remote server
cnx.Properties("Password").Value = "XXX"
cnx.Open "Data source=YYY;User ID=ZZZ;Initial Catalog=TEST"
Set cmd.ActiveConnection = cnx
cmd.CommandText = "Insert into test (value1,value2,formula) " & _
"select * " & _
"from OpenRowSet ( " & _
"'Microsoft.Jet.OLEDB.4.0' " & _
" , 'Excel 8.0; DATABASE=C:\path\book1.xls;HDR=YES' " & _
" , 'select value1,value2,formula from [Data$]' )"
cmd.Execute
There are three fields value1,value2 and formula in the test table
and headers in the Excel spredsheet Data
The formula field looks like '=A2+B2 etc which will insert =A2+B2
Gosta
October 26, 2005 at 6:19 pm
Thank you very much! I will give this a try and see what happens. Again, much appreciated!
October 27, 2005 at 9:08 am
Hi,
There is another way to solve the need. The formula can then be in its original form =A3+B3 in column 3.
You can run an Excel makro like:
Sub test()
Dim K As Long
Dim lastrow As Long
Dim cnx As ADODB.Connection
Set cnx = New ADODB.Connection
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cnx.Provider = "sqloledb"
cnx.Properties("Network Library").Value = "DBMSSOCN" 'Remote server
cnx.Properties("Password").Value = "XXX"
cnx.Open "Data source=YYY;User ID=ZZZ;Initial Catalog=TEST"
Set cmd.ActiveConnection = cnx
'If needed truncate table Test before insert new values etc.
cmd.CommandText = "Truncate table Test"
cmd.Execute
Worksheets("Data").Select
'Find the last row
Selection.SpecialCells(xlCellTypeLastCell).Select
lastrow = ActiveCell.Row
For K = 2 To lastrow 'Do insert from row two to the last row. Use .Formula to insert the formula
cmd.CommandText = "insert into test values (" & Cells(K, 1).Value & "," & Cells(K, 2).Value & ",'" & Cells(K, 3).Formula & "')"
cmd.Execute
Next K
End Sub
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply