Member Avatar for ShadowScripter

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
Edited by ShadowScripter because: n/a
  • 1 Contributor
  • 1 Reply
  • 359 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by ShadowScripter
Member Avatar for ShadowScripter

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) :$

Edited by ShadowScripter because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.