Solved HELP With Access to Excel

[MatthewM]

I have an MS Access DB that pulls records from two tables (FYI this is for inventory). Boss has a premade Excel workbook that the information needs to go into. I use a Query to make sure that the data is from where she needs. then i have a VBA to export it out to the excel workbook, but Access says that it is to big.... I have broken it up into 2 Subs() (Sub Aug01 (), Sub Aug02 ()). I need help making it all export out to the same excel workbook. How I have it set right now is it opens the same workbook twice. I can't figure out how to get it to run all on the same workbook... below is part of the code that i use (the only thing that changes is the range for the Excel). I would not mind a loop until the bar-code is null, because all items in the record have a bar-code. :banghead::banghead::banghead::banghead::banghead:

Private Sub AUG01()
 
DoCmd.GoToRecord , "", acFirst
  
Dim objXLApp As Object
Dim objXLBook As Object
 
Set objXLApp = CreateObject("Excel.Application")
objXLApp.Application.Visible = True

Set objXLBook = objXLApp.workbooks.Open("S:\newborn screening\Administrative NBS\01 NBS Admin\Items Being Worked on (Matt)\LD4 list\LD4a MM TB Diagnostic - Master.xlsx")


  With objXLApp
  .Visible = True
  .sheets("Aug").select

  End With
  
objXLBook.activesheet.range("A2") = "FY " & Me.[month1]


objXLBook.activesheet.range("A5") = Me.[item #]
objXLBook.activesheet.range("B5") = Me.[Item]
objXLBook.activesheet.range("C5") = Me.[Notes]
objXLBook.activesheet.range("D5") = Me.[Unit Type]
objXLBook.activesheet.range("E5") = Me.[Cost]
objXLBook.activesheet.range("F5") = Me.[Total]
objXLBook.activesheet.range("h5") = Me.[Barcode]



If (Not IsNull(Me.Item.Value)) Then
objXLBook.activesheet.range("A6").EntireRow.Insert xlDown

On Error GoTo errorhandler

DoCmd.GoToRecord , "", acNext

End If

errorhandler:


End Sub

 

[theDBguy]

Hi Matthew. Welcome to AWF! I don't get it. What was "too big?"

 

[vba_php]

then i have a VBA to export it out to the excel workbook, but Access says that it is to big....

Matthew, guy is more of an expert than me in something like this, but just a couple things:

1. if you are trying to run a lot of *activesheet.range() =* statements, that would more than likely make any application that's interfacing with another very angry, not to mention being incredibly slow (I would assume). I'm sure there's a better way you can do that. for instance, you seem to be doing it already with your attempt at this:

   objXLBook.activesheet.range("A6").EntireRow.Insert xlDown

*xlDown* and specs like it work, unless you run into a blank row, and then its effort is killed. I'm referring to a separate xlDown operation here though. Yours code is just a tad different than what I've done.

• have you looked into using an ADO connection instead of CreateObject()? That might be relevant, not sure.
• Have you attempted to use the *transferspreadsheet* method in VBA? That also might be worth looking into.

 

[MatthewM]

Hi Matthew. Welcome to AWF! I don't get it. What was "too big?"

Guy,

What i posted was just a small part of the code. It runs until row 165 or the data is blank.

 

[theDBguy]

Guy,

What i posted was just a small part of the code. It runs until row 165 or the data is blank.

No, I mean, you said Access complained that "it was too big." My question was what was it that was too big?


Edit: Or, did I misunderstand your original post? Were you saying you couldn't post all your code because this forum's software complained that it was too much to post?

 

[MatthewM]

No, I mean, you said Access complained that "it was too big." My question was what was it that was too big?

Sorry I did not understand but now I got you....

The Error that I get is "Compile error: Procedure too large"

 

[Cronk]

That error message is indicative of the procedure being too large ie too many lines of code. BTW, if the code doesn't compile, how do you have 165 lines being exported?

 

[theDBguy]

Sorry I did not understand but now I got you....

The Error that I get is "Compile error: Procedure too large"

Okay, that makes sense now. What you could try to do is break your one big procedure into two smaller procedures and just call the second procedure from the first one.

 

[MatthewM]

Okay, that makes sense now. What you could try to do is break your one big procedure into two smaller procedures and just call the second procedure from the first one.

Do you know of a way that i can send it to the already open excel sheet. right now it opens two.

 

[MatthewM]

That error message is indicative of the procedure being too large ie too many lines of code. BTW, if the code doesn't compile, how do you have 165 lines being exported?

I wrote the code to go down to 165 lines. If it does not then the code stops. I have broken it up into two Sub()'s but I can not get it to go into one excel workbook. It opens two of the same sheet.

 

[Deleted member 147267]

BTW, if the code doesn't compile, how do you have 165 lines being exported?

My guess would be that the code was written and compiled (and perhaps even tested) using 64 bit Access but is being run on a 32 bit version when this error is raised.

 

[theDBguy]

Do you know of a way that i can send it to the already open excel sheet. right now it opens two.

Yes, you could try passing the same object (Excel instance) to the other procedure.

 

[Deleted member 147267]

You ought to be testing for the existence of the workbook and/or sheet before deciding which path to take. Look at your Task Manager list before running your procedure. If you're reporting duplicates without having shut down Windows in the process, you could have left open a version along the way. Maybe you should post the entire procedure for more focused assistance.

 

[The_Doc_Man]

According to what I could look up, a module is limited to 64kb when compiled, and the guess is that this is over 1k lines. You showed us one subroutine... but what else is in the code module? And is that sub the last entry in the module?

 

[Deleted member 147267]

I don't dispute that but I did sort of follow Cronk's line of thought when I posted about bitness. In other words, if it was compiled (we don't know that) it must have been done in a 64b version. If it was too large, wouldn't you think that it would not run through 165 steps/lines before generating the error?

A VBA macro that was created for a 32-bit version of an Office application can run on the same 64-bit version of an Office 2010 program and most VBA macros written for the 64-bit version of a program will run on the 32-bit version of the program. However, a macro that is too large will fail together with the Visual Basic for Applications error message that is mentioned in the "Symptoms" section.

The article does use the word 'procedure' so I am discounting the oft misleading use of the word 'macro'.

https://docs.microsoft.com/en-us/office/troubleshoot/office/compile-error-procedure-too-large

 

[MatthewM]

According to what I could look up, a module is limited to 64kb when compiled, and the guess is that this is over 1k lines. You showed us one subroutine... but what else is in the code module? And is that sub the last entry in the module?

Attached is the full code at range A110 is where i made a new Sub Aug02()

 

[Deleted member 147267]

Interesting that as a notepad file it's 164Kb; 24Kb as a Word document. Personally I think navigating through a form instead of a recordset is the major problem. Rather than expound on that, I'll wait to see if there's any agreement.

 

[Cronk]

Matthew, in all programming languages (at least all that have any sophistication) including VBA, loops are used to repeat the same code operation. Something like

for i = 1 to HoweverManyRecords
   objXLBook.activesheet.range("A" & (i+4)) = Me.[item #]
   etc....
next i

However, you could also use the copyFromRecordset method

dim rst as recordset

set rst = db.openrecordset("select * FROM YourTableOrQuery")
'--Replace YourTableOrQuery with whatever is your form's recordsource

objXLApp.sheets("Aug").Range("A5").copyFromRecordset rst

Micron, I don't think navigating through a form is a problem but it's certainly clumsy and probably slower than looping through a recordset.

 

[Deleted member 147267]

Micron, I don't think navigating through a form is a problem but it's certainly clumsy and probably slower than looping through a recordset.

It would appear to be the problem seeing as how I meant the method requires copious repetition of basically the same code. I did say "instead of a record set" which I meant to imply looping rather than navigating. Sorry I wasn't more clear but Momma was waiting to go out. You probably know what that's like.

 

[Cronk]

You probably know what that's like

Too right. For me, too many times a click in haste, red face. (I have a 10 minute delay on sending emails after clicking send.