PDA

View Full Version : Linking to Excel when Excel is not Installed



Anne Troy
06-15-2004, 08:02 AM
Can I use VBA to grab data into Word from an Excel spreadsheet on a server if I don't have Excel installed on *this* PC?

mark007
06-15-2004, 08:12 AM
No. You could download one of the free office suites like sun star office and open the excel file. Of course the free office suites don't support VBA.

:)

Anne Troy
06-15-2004, 08:15 AM
Okay. I'm asking for someone else, so this should help them make their decisions.

Thanks!

Tommy
06-15-2004, 09:10 AM
mark007
Can't you access excel through ODBC?

Anne Troy
06-15-2004, 09:18 AM
That's why I'm asking you guys. I don't know, and I have no access to a network, and I've got Excel on here 4 times. LOL.

mark007
06-15-2004, 09:24 AM
Very true - if the Excel spreadsheet was merely set-up as a database i.e. column headers in row1, data in the rest of it then yes you could use it as a database and could get the data.

mark007
06-15-2004, 09:29 AM
Fo example you could use ADO with a connection string:


oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\workbook.xls;Extended Properties=""Excel 9.0;HDR=Yes"""

"HDR=Yes" means that there is a header row so the first row will not be included in the recordset. Obviously set it to no if there isn't a header row.

:)

Anne Troy
06-15-2004, 09:29 AM
So... I would NOT need to have Excel installed?

mark007
06-15-2004, 09:32 AM
Nope you shouldn't need it I don't think though it's not something I've tried. Everyone should have Excel - it rocks!

:D

THA
06-15-2004, 09:45 AM
See http://ozgrid.com/forum/forumdisplay.php?f=29
where you find numerous example about it.

THA

Anne Troy
06-15-2004, 09:49 AM
The question is not HOW to do this.
It is WHETHER it can be done WITHOUT Excel installed on the client's PC.

Tommy
06-15-2004, 10:03 AM
Ok now you got me curious, soon as I find a pc without Excel and does have Word, I will check it out. I got enough spare parts at the house figure it out. I will try and let ya'll tommorrow.

Anne Troy
06-15-2004, 10:09 AM
Too cool, Tommy. :) That'd be fantastic. As soon as I saw this Q (elsewhere), I just had to know myself. I didn't THINK so, but hey...

THA
06-15-2004, 10:56 AM
Since no call is required to Excel's library only to the ADO-Library then the question is answered per se and the answer is not no.

THA

___
06-15-2004, 10:58 AM
I think it's possible, I'll try something tomorrow at work.

jamescol
06-17-2004, 09:14 PM
Answer is definitely yes; you can use either the Jet ODBC driver as mentioned above or the MS OLE DB ODBC driver. Note: You MUST use the Jet 4.0 version; earlier versions will not work. You cannot open a workbook if it is password protected, even by supplying the password in the connection string.

Also, it is important to understand that Excel guesses at the data type in the spreadsheet. It does this by reading the first 8 rows (default, but you can change) and using a set of rules basically comparing the number of characters vs. numbers in a cell (oversimplified).

Samples:

To setup and open the connection


'Declare the connection variable
Dim cn as ADODB.Connection

'Create the connection object
Set cn = New ADODB.Connection

'Set the connection properties
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"

'Note on Excel Version: Specify Excel 5.0 for an Excel 95 workbook (version
'7.0 of Excel), and Excel 8.0 for an Excel 97, Excel 2000, or Excel 2002 (XP)
'workbook (versions 8.0, 9.0, and 10.0 of Excel).
'By default, the Jet provider assumes the Excel workbook's first row
'contains column headings. If this is not the case, modify the connection
'string to include HDR=No, as in the sample below. Note the syntax with
'the quote marks - it is correct!
'.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
'"Extended Properties=" " Excel 8.0; HDR=No;" " "


.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=Excel 8.0;"

'Open the connection
.Open
End With


If you don't like the Jet 4.0 driver, you can use the MS OLEDB ODBC driver instead. However, there are several bugs with the driver when using it to connect to Excel, and you would need to install hotfixes to use it effectively.


Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "MSDASQL"

'If you only need read-only access, remove the ReadOnly=False
'statement. This connection is read-only by default

.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=C:\MyFolder\MyWorkbook.xls; ReadOnly=False;"
.Open
End With

Anne Troy
06-17-2004, 09:18 PM
Excellent, James. The person has been asking about this. I suggested they join the forum and upload a sample of the Word/Excel files at least...hasn't done so yet, but that was only earlier Thursday.