Application.ScreenUpdating...

[RubberChicken]

[SOLVED]Application.ScreenUpdating...

Hi all,

I have inherited a Access database that exports data to Excel. It uses a combination of sql and VBA to produce the reports in Excel.

I want to try and speed up some of the exports as they are quite large and can take a while to complete. I have tried using Application.ScreenUpdating = False but get a "Method or data member not found" error. I suspect this is because I am missing something in the reference library but when I check nothing is noted as "missing" and all the obvious Microsoft options are ticked.

I have tried using the DoCmd.Echo False but this has no impact on the exporting and I can still see the Excel report populating.

Any ideas?

Thanks
​

 

[RubberChicken]

I think I might have posted this question into the wrong board.
Please could one of the mods move it to the Modules & VBA section?

Thanks

 

[pr2-eugin]

Application.ScreenUpdating is a method in Excel I guess.. Try Application.Echo instead of DoCmd.Echo..

 

[RubberChicken]

Thanks Eugin,

I have tried both of those commands but it makes no difference. When Access is exporting the data to Excel I can still see the spreadsheet tabs populating.

I was wondering if it was where I am putting the commands. I have tried right at the start and also when the workbook is being called but it makes no difference.

 

[pr2-eugin]

Not sure as I have not played with Access and Excel much.. Have you tried setting the Visibility of the Excel file = False?

 

[pbaldy]

I moved your thread as requested. By the way, it may help to see your code. If you're using automation and you have a .Visible at the beginning, try moving it to the end.

 

[SOS]

If you're using automation and you have a .Visible at the beginning, try moving it to the end.

That would be my first thought too.

 

[Pat Hartman]

Somewhere you have code similar to the following that defines the excel object:

Public YourExcelApp As Excel.Application

Then AFTER you open a workbook use the following to hide it.

YourExcelApp.Visible = False

 

[SOS]

Somewhere you have code similar to the following that defines the excel object:

Public YourExcelApp As Excel.Application

Then AFTER you open a workbook use the following to hide it.

YourExcelApp.Visible = False

A new Excel Application instance is, by default, hidden. You have to make it visible for it to be visible. So, if setting its visible property to false hides it then there is some code somewhere that made it visible to begin with. Hence what pbaldy and I wrote.

 

[Pat Hartman]

I made the first post from memory. I looked up the code I used and I see that I made the workbook visible so it must have opened hidden. I made it visible to keep the user entertained while the spreadsheet was formatting since it was only one sheet and it didn't take too long. I have Word automation that can create thousands of documents and for those I obviously leave Word hidden.

Public appExcel As Excel.Application
Public wbkNew As Excel.Workbook
Public wksNew As Excel.Worksheet
...
 Set appExcel = Excel.Application
 Set wbkNew = appExcel.Workbooks.Add
 Set wksNew = appExcel.Worksheets("Sheet1")
 
 appExcel.Visible = True

 

[RubberChicken]

Thank you all for your input. I went with moving the .visible to the end of the script. It shaved over 2 mins off a 10.30min report and I will be trying the same on some of the larger reports.
I was very impressed when I came in this morning and saw all your responses. It is cool to be part of such and active and sharing community. I look forward to learning more from you all and hopefully becoming a helpful member myself one day.

Many thanks
Nik

 

[RubberChicken]

Quick question... How do I mark this post as solved?

Thanks

 

[pbaldy]

Happy to help. I think the solved thing is covered here:

http://www.access-programmers.co.uk/forums/showthread.php?t=225177