• 0

[VBA] Progress report in access while running excel macro


Question

I am currently using an Access database + frontend to control excel manipulations, so I regularily run excel macros from Access. These excel macro's can take anywhere from 10 seconds to 10 minutes, depending on the size of the excel files that I am working with. As they are running, I would like to show the user what has been done, sort of like a progress bar in Access. I already have a form loading to tell them it might take some time using a DoEvents statement, but I feel this really limits what I can do. Is there any way I can read progress in from excel as it is running the macro?

Link to comment
Share on other sites

11 answers to this question

Recommended Posts

  • 0

This thought crossed my mind, but then I would not be able to read this information until after the excel macro finishes, which defeats the purpose. Thanks for the help though.

Link to comment
Share on other sites

  • 0

Hi you can control access from excel at the same time as your controlling excel from access... you can use this to pass values (such as progress indicators) back to the access front end.

Also if you have a macro taking 10minutes, you need to look at how its coded...

Link to comment
Share on other sites

  • 0
Hi you can control access from excel at the same time as your controlling excel from access... you can use this to pass values (such as progress indicators) back to the access front end.

Also if you have a macro taking 10minutes, you need to look at how its coded...

The reason some of them can take 10 minutes is on extreme cases I have 50000 records, with around 50 different columns where I am doing a lot of work and checks and formatting. I am utilizing every excel trick in the book to speed the process up, so its the best I can get.

Because I am running the access front-end, an instance of the access is already running. This means that I cannot open it from excel because it wants to open a new instance, something that is completly worthless for me.

I have tried making a connection to the opened access file, however this errors out when I try to open or manipulate anything from access.

Link to comment
Share on other sites

  • 0

Hm,

50,000 records each containing 50 records... I hope your loading them into arrays.

Also switch calculation to manual during your script, using evaluate to perform any calculations, this should speed your processing up as it wont try recalculating the document everytime you make anything happen.

Also all Office apps can talk between each other, so I fail to see why you'd have an issue firing data between two open documents.

Link to comment
Share on other sites

  • 0
Hm,

50,000 records each containing 50 records... I hope your loading them into arrays.

Also switch calculation to manual during your script, using evaluate to perform any calculations, this should speed your processing up as it wont try recalculating the document everytime you make anything happen.

Also all Office apps can talk between each other, so I fail to see why you'd have an issue firing data between two open documents.

See, thats what I would assume too, but hours of searching google, and the only thing I can come up with is how to open a database frontend or an excel file and talk to it, not talk to one that is already open.

Link to comment
Share on other sites

  • 0

I'm not familiar with running Excel macros through Access, so I don't know exactly how you call/run the macro.. but there should be some way of checking if the macro is still running from Access...

Just add a while loop inside the Access db to check a specific cell in the Excel sheet for 'progress'... something like

NOT REAL CODE:
While (macro.stillExecuting = True)
 Status.Text = GetCellData('A255')
Wend

Then in the Excel sheet you update the progress of your macro into cell A255 and have a lot of DoEvents calls if you are using loops... but I'm sure there's a way of doing this via DDE...

Link to comment
Share on other sites

  • 0
I'm not familiar with running Excel macros through Access, so I don't know exactly how you call/run the macro.. but there should be some way of checking if the macro is still running from Access...

Just add a while loop inside the Access db to check a specific cell in the Excel sheet for 'progress'... something like

 
Set xlObj = CreateObject("excel.application")

sFullPath = CurrentProject.path & "\ABLtemplate.xls"
Set wbkSource = xlObj.Workbooks.Open(sFullPath)


Set wbkTarget = xlObj.Workbooks.Add


If Not CurrentProject.AllForms("Main").IsLoaded Then
    DoCmd.OpenForm "Main", acNormal
End If

DoCmd.OpenForm "frmRunning", acNormal
DoEvents

If macro_run(1) Then
'this is where I run the excel macro
     xlObj.Run "ABLtemplate.xls!Member"
     xlObj.DisplayAlerts = False
    wbkSource.ActiveSheet.Copy after:=wbkTarget.Worksheets(wbkTarget.Worksheets.Count)
End If

Instead of using DDE I use the office automation, which works perfectly for my needs. As the "ABLtemplate.xls!Member" is running, the Access is frozen waiting for the macro to be done, so then it can continue on to the next line. This is why I don't believe it to be possible for me to do more work in accees, reading from cells in the excel file I opened.

Trying to initiate a connection from the running excel macro to the already open access file and then sending data that way is all I can think of, and I haven't yet found a way of doing this.

Link to comment
Share on other sites

  • 0

Solved it, it seems like a dirty solution so I might have to fine tune it but it works like it should. I was missing the RepaintObject method, thats why it was not working before.

Access.Application.Forms("frmRunning").lblMsg.Caption = rsTemp![CustomName]

Access.Application.DoCmd.RepaintObject acForm, "frmRunning"

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.