Automation with Excel

[Joakim N]

Hi,



I am not sure if this is the right forum to ask this question. I hope someone recognise the issue.

I am making my reports (quite complicated for Access) in Excel. I invoke Excel from VBA Access. My issue is that on some (not all) computers I get error on line "Set gappExcel = New Excel.Application"

Prerequisites:
- Reference to Excel "Microsoft Excel 16.0 Object Library"
- Everyone has Excel 2016 & Acess 2016 where tested. (If different versions it might be a Early/Late binding issue. But everyone has the same version. For info I have used Early Binding)

My code:

Public gappExcel As Excel.Application

Sub OpenExcelApplication()

    'On some computer there is Run-Time 13 here
    Set gappExcel = New Excel.Application

End Sub

Anyone having an idea what to do?

[June7]

What makes the reports so complicated cannot be done in Access?

What is the exact error message?

Do you need to just open the Excel file or will your Access VBA need to manipulate the file?

Try: Set gappExcel = CreateObject("Excel.Application")

Review: https://www.accessforums.net/showthread.php?t=65761

[Joakim N]

Hi,

I have attached a report as an example why I do the reports in Excel. I haven't found a way to do them in Access. All data is stored in tables in Access.

Error message: Run-time error 13, "Type Mismatch"

On my computer I never get the error message. It's about 50% of other computers. It has been working on some other before and now it doesn't. Not changed any Office version.

If I use "Set gappExcel = CreateObject("Excel.Application")" I get the same error as "Set gappExcel = New Excel.Application" on other computers where nothing works. On my computer both works.

[June7]

Well, can't begin to guess why some comps have that issue and others don't. Doesn't seem to be anything terribly unusual about the sheet and no VBA code. How are you controlling cell colors? Don't see conditional formatting so this is manual? Don't even see any formulas in cells.

Think I can see why Excel may be the better tool. However, you might find this thread interesting https://www.accessforums.net/showthread.php?t=18459. Unfortunately, some of the referenced threads no longer exist but the databases are still attached.

[Joakim N]

Hi,

I found a way to solve this. However, I have no idea why it works now. It seems It cleaned up something. I just:
1. Exported all modules
2. Removed all moules in Db
3. Imported all modules back

Then it worked. The code is not changed a bit.

Answer to June7: Yes, the color of cells are made by VBA code. No conditional formatting.