Results 1 to 3 of 3
  1. #1
    DubCap01 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Location
    Dubbo, Australia
    Posts
    104

    Excel worksheets will NOt link!

    Hi all,

    I can go through the wizard, and I can link a worksheet from an existing Excel workbook, but when I try and automate it in VBA, it won't link the worksheet as a Linked Excel table - it imports the table!
    As I need to add records to this Excel sheet, and then disconnect the link, I find this very frustrating! Do I need to add some more code to then save the Excel workbook before any of this will work properly?


    Everything but the LINK (as opposed to imported) seems to be working...

    Thanks in anticipation everyone!

    Code:
     
           DoCmd.TransferSpreadsheet acLink, , "TblTmpBilling", "Z:\HealthIntelligenceUnit\Data\ICPDATA.xlsx", True, "TblTmpBilling"
            DoCmd.RunSQL "DELETE * FROM TblTmpBilling;"
            DoCmd.RunSQL "SELECT TblBilling.RegisterID, TblBilling.ContactDate, TblBilling.BillingCode, TblBilling.ExportFlag, *" & _
                            "INTO TblTmpBilling FROM TblBilling WHERE (((TblBilling.ExportFlag)=0 Or (TblBilling.ExportFlag) Is Null));"
            cdb.Execute "DROP TABLE TblTmpBilling", dbFailOnError

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Its possible you need the ODBC driver for Excel. It would be an option to install on the Access install disk.

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    What is the overall reason to want to do this?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-10-2015, 11:53 AM
  2. Exporting to Excel - All Worksheets Highlighted
    By kristyspdx in forum Import/Export Data
    Replies: 3
    Last Post: 02-26-2013, 05:42 PM
  3. How to copy and paste between worksheets in Excel?
    By Ronald Mcdonald in forum Programming
    Replies: 6
    Last Post: 05-26-2012, 10:40 PM
  4. How To Combine 3 Linked Excel Worksheets?
    By cadsvc in forum Access
    Replies: 2
    Last Post: 04-26-2011, 10:16 AM
  5. Deleting Worksheets on a Excel workbook
    By BED in forum Programming
    Replies: 0
    Last Post: 07-27-2010, 01:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums