Results 1 to 9 of 9
  1. #1
    crimedog is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    141

    Import data into TempExcel Table from Open Excel file

    No matter how the file was received - Email, downloaded from cloud etc...
    I want to be able to have the spreadsheet open...
    Then run vba in Access (On click)

    Import the data from worksheet "data" into Table "TempExcel"

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Is there a question in there that I don't see?

  3. #3
    crimedog is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    141
    I would like to know how to reference a spreadsheet that is open but not saved on my computer.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922

  5. #5
    crimedog is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    141
    yeah - I saw that...
    "you need to set a reference (Menu: Tools->References) to the Microsoft Excel Object Library then you can access all Excel Objects.
    I can't find Menu: Tools->References in 2010??

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Open the IDE (ctl-G) (aka VBA editer)
    Then Tools->References

  7. #7
    crimedog is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    141
    wow, yeah that was a moment. Yes there it is in the VBA editor. Thanks

  8. #8
    crimedog is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    141
    Ok this is what I am cobbling together as vba for a form
    Code:
    Option Compare Database
    Option Explicit
     Dim objExcelApp As Object
     Dim wb As Object
      Sub Initialize()
        Set objExcelApp = CreateObject("Excel.Application")
      End Sub
    Private Sub Command2_Click()
      Dim ws As Object
      Dim XLApp As Excel.Application
     
      Set wb = XLApp.ActiveWorkbook
      Set ws = wb.Sheets("Master")
     DoCmd.TransferSpreadsheet acImport, , "ImportFromExcel", ActiveWorkbook, True
    End Sub
    I am looking at several different sites. I have the References set for Excel objects, so far it does not seem like I am close

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might look at Ken Snell's site
    http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

    Lots of examples

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

Similar Threads

  1. Replies: 1
    Last Post: 08-18-2016, 11:44 AM
  2. Import Excel file into a table
    By lucky33 in forum Import/Export Data
    Replies: 2
    Last Post: 09-03-2014, 01:17 PM
  3. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  4. Replies: 5
    Last Post: 01-29-2013, 06:00 PM
  5. Import Data from Excel File
    By zwieback89 in forum Import/Export Data
    Replies: 1
    Last Post: 06-27-2012, 08:44 AM

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