1,105,625 Community Members

MS ACCESS - VBA Getting data from excel workbook and put in as records in database

Member Avatar
ShadowScripter
Junior Poster in Training
94 posts since Apr 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 6 [?]
Skill Endorsements: 0 [?]
 
0
 

I think the title says most of it.

How do I, in MS Access using VBA, get information from a workbook which I can then put in as a recordset in a table?

I know that excel has an easy workaround for other excel workbooks, and I've read somewhere how to get recordsets from access and input them to excel

Excel to excel:

Dim wb As Workbook
    Dim path As String
    path = "D:\Documents\etc\Book1.xls"

    Application.ScreenUpdating = False    
    Set wb = Workbooks.Open(Path, True, True)
    With wb
      'do stuff here
    End With
    wb.Close False
    Set wb = Nothing
Member Avatar
ShadowScripter
Junior Poster in Training
94 posts since Apr 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 6 [?]
Skill Endorsements: 0 [?]
 
0
 

Referencing the Microsoft Office Object Library gives me access to the MS Excel object library (including objects like workbook and worksheet)

(VBE -> Tools -> References -> Microsoft Office Object Library)

Here is an example I found, using a similar method, but inserting recordsets through excel to access instead. Same principle I suppose
Export data from excel to Access using VBA

Given the same object control I have in Excel, I should be able to access workbooks and work with that data inside MS Access.
I realize now that I have a huge amount of control over all Office components through VBA.

I don't know if its allowed to double post, but I can't edit my first post, so I'll add some of my findings.
I write this for others that might also have the same problem.

(Solved) :$

Question Self-Answered as of 3 Years Ago
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: