2

I have an Excel user form and some Excel VBA code that transforms some of the worksheet data into a format ready for import to an Access application. The code works fine but I've decided that it would be much better for development and operations if the form and code were moved over to reside within the Access application, rather than the spreadsheet.

Where can I learn how to convert my Excel VBA code to work within Access or at least where is a reference on coding to process Excel within Access VBA?

Based solely on VBA ACCESS - Loop Excel workbooks I have so far managed to start with this (Note: all of the following are just snippets.):

Private source_empl_col As Integer
Private xl_app As Object

Set xl_app = CreateObject("Excel.Application")
xl_app.Workbooks.Open (workbook_txt)

and I've managed to convert 

Private source_sheet As Worksheet

Set source_sheet = Worksheets(source_cbo.Text)
source_sheet.Activate

Dim ws As Integer
For ws = 1 To Worksheets.Count
    source_cbo.AddItem Worksheets(ws).Name

from Excel VBA into Access VBA

xl_app.Sheets(source_cbo).Activate

Dim ws As Integer
For ws = 1 To xl_app.Worksheets.Count
    source_cbo.AddItem xl_app.Worksheets(ws).Name

without error. However, when it comes to converting

source_empl_col = Range(empl_col_txt & 1).Column

from Excel VBA into Access VBA

source_empl_col = xl_app.Sheets(source_cbo).Range(empl_col_txt.Text & 1).Column

or even just

source_empl_col = xl_app.Sheets(source_cbo).Range("B1").Column

I get

Run-time error: '91'
Object variable ... not set.

There's just too much code that I need to translate so it works within Access without knowing what I'm doing. Some guidance is needed!

Erik A
  • 31,639
  • 12
  • 42
  • 67
Martin F
  • 590
  • 7
  • 28
  • 1
    Finally! Someone who sees spreadsheets are not databases and not user interfaces and not scalable in application and automation environments! – Parfait Nov 14 '15 at 02:47
  • Not quite sure where you're coming from, @Parfait, but i'll take it as a compliment. – Martin F Nov 16 '15 at 20:28

1 Answers1

3

In Access VBA, to utilize the object model of external Office applications, namely here Excel (but also same rules apply for Outlook, PowerPoint, Word, etc.) requires explicitly declaring foreign objects.

What you need is an explicit declaration and setting of the workbook object. You jumped from app directly to sheet, forgetting the middle man -workbook.

Dim xl_app As Object, xl_wkb As Object
Dim source_empl_col As Integer

Set xl_app = CreateObject("Excel.Application")
Set xl_wkb = xl_app.Workbooks.Open (workbook_txt)

source_empl_col = xl_wkb.Sheets(source_cbo).Range(empl_col_txt.Text & 1).Column

Alternatively, you can even set the worksheet object:

Dim xl_app As Object, xlwkb As Object, xl_wks As Object
Dim source_empl_col As Integer

Set xl_app = CreateObject("Excel.Application")
Set xl_wkb = xl_app.Workbooks.Open (workbook_txt)        
Set xl_wks = xl_wkb.Sheets(source_cbo)

source_empl_col = xl_wks.Range(empl_col_txt.Text & 1).Column
Parfait
  • 104,375
  • 17
  • 94
  • 125