Consulting

Results 1 to 17 of 17

Thread: Linking to Excel when Excel is not Installed

  1. #1
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location

    Linking to Excel when Excel is not Installed

    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?
    ~Anne Troy

  2. #2
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    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.

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  3. #3
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Okay. I'm asking for someone else, so this should help them make their decisions.

    Thanks!
    ~Anne Troy

  4. #4
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    mark007
    Can't you access excel through ODBC?

  5. #5
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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.
    ~Anne Troy

  6. #6
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    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.
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  7. #7
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    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.

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  8. #8
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    So... I would NOT need to have Excel installed?
    ~Anne Troy

  9. #9
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    Nope you shouldn't need it I don't think though it's not something I've tried. Everyone should have Excel - it rocks!

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  10. #10
    VBAX Newbie
    Joined
    Jun 2004
    Posts
    4
    Location
    See http://ozgrid.com/forum/forumdisplay.php?f=29
    where you find numerous example about it.

    THA

  11. #11
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    The question is not HOW to do this.
    It is WHETHER it can be done WITHOUT Excel installed on the client's PC.
    ~Anne Troy

  12. #12
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    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.
    Last edited by Tommy; 06-15-2004 at 10:05 AM. Reason: fat fingers

  13. #13
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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...
    ~Anne Troy

  14. #14
    VBAX Newbie
    Joined
    Jun 2004
    Posts
    4
    Location
    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

  15. #15
    VBAX Regular ___'s Avatar
    Joined
    Jun 2004
    Posts
    22
    Location
    I think it's possible, I'll try something tomorrow at work.
    Nemo hic adest illius nominis
    ??????????????????
    ??????

  16. #16
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    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]
    "All that's necessary for evil to triumph is for good men to do nothing."

  17. #17
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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.
    ~Anne Troy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •