VBA: Creating Sub Procedures with Excel Workbook (1 Viewer)

P

Petran

Registered User.
Local time
Today, 22:55
Joined
Oct 25, 2011
Messages
11
I am in the process of creating an Excel report, using VBA and data that resides in the MS Access database. This is quite a large report and I get the error that the Procedure is too large and I should break it up in procedures.
Under normal procedures this is not diffcult, but working with an Excel application and related Workbook and Sheets, is quite a challenge. An example of my problem is, how does the called Sub Procedure know that I am working with an Excel Application object? Is there someone out there that can assist please. . .:banghead:
 
June7

June7

AWF VIP
Local time
Today, 11:55
Joined
Mar 9, 2014
Messages
5,466
I have never seen such an error message.

Perhaps globally declare the Excel object variables or pass the Excel objects as procedure arguments.

Why do you need to do report in Excel?
 
P

Petran

Registered User.
Local time
Today, 22:55
Joined
Oct 25, 2011
Messages
11
Is is a Client Requirement and it is the first time that I have seen it as well, but is is appening. The error message just says Compile error. Procedure too large. . . .
 
isladogs

isladogs

MVP / VIP
Local time
Today, 20:55
Joined
Jan 14, 2017
Messages
18,209
Just to clarify, is the procedure
a) done in Access to create a report in Excel
b) done in Excel pulling data from Access

Also is the procedure running in a (possibly infinite) loop?
Have you tried stepping through the code?

I've also never seen the message.
In fact, looking at all Access error codes, I can't find an error like that.
Please provide a screenshot showing error number and description.
 
Last edited:
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 15:55
Joined
Jun 21, 2011
Messages
5,900
Hmm, wondering if any of that code contains formatting of Excel. If it does perhaps you could create a Template, send the data and then do a Save As. That could possibly eliminate quite a few lines of code.
 
P

Petran

Registered User.
Local time
Today, 22:55
Joined
Oct 25, 2011
Messages
11
The problem was solved::)

June7: the suggestion of Global defines did the trick, thanks
ridders: The report is generated in MS Access, using data from the MS Access Database. I have attached a screenshot of the error message. . .
GinaWhipp: it is indeed a lot of formatting for the Excel report. Cannot create a template as the report content can vary, depending on the data.
Gasman: Thanks for your contribution
 

Attachments

  • LargeProc.docx
    60.7 KB · Views: 47
P

Petran

Registered User.
Local time
Today, 22:55
Joined
Oct 25, 2011
Messages
11
How can I 'Close' this thread. It was resolved. . . .
 
isladogs

isladogs

MVP / VIP
Local time
Today, 20:55
Joined
Jan 14, 2017
Messages
18,209
Interesting.
As I said, this isn't included in the list of Access error codes.
If it's caused by having procedures larger than 64K in size, then I see no reason why using global variables would fix it without shortening the code

Anyway, if it's now working for you, then fine.
To mark the thread solved, use the Thread Tools drop down in the blue menu bar at the top.
 
You must log in or register to reply here.

Similar threads

J
Solved Catch error from Access-In procedure that was started with Appliction.Run
Replies
9
Views
216
Josef P.
J
R
Updating Excel spreadsheet from within the Access
Replies
8
Views
325
CJ_London
C
oxicottin
Solved Export to Excel error '1004' With time format
Replies
9
Views
207
ebs17
E
W
Check for open excel workbook and if open take control
2
Replies
23
Views
968
ebs17
E
A
converting early binding to late binding
Replies
10
Views
385
Josef P.
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom