Linking to Excel when Excel is not Installed

[Anne Troy]

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]

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]

Okay. I'm asking for someone else, so this should help them make their decisions.

Thanks!

[Tommy]

mark007
Can't you access excel through ODBC?

[Anne Troy]

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]

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]

Fo example you could use ADO with a connection string:

[vba]
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\workbook.xls;Extended Properties=""Excel 9.0;HDR=Yes"""
[/VBA]
"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]

So... I would NOT need to have Excel installed?

[mark007]

Nope you shouldn't need it I don't think though it's not something I've tried. Everyone should have Excel - it rocks!

[THA]

See http://ozgrid.com/forum/forumdisplay.php?f=29
where you find numerous example about it.

THA

[Anne Troy]

The question is not HOW to do this.
It is WHETHER it can be done WITHOUT Excel installed on the client's PC.

[Tommy]

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]

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]

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

[___]

I think it's possible, I'll try something tomorrow at work.

[jamescol]

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
[vba]

'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
[/vba]

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.

[vba]
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

[/vba]

[Anne Troy]

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.