Very very slow startup of a xlsm spreadsheet

[gray_b]

I am looking for advice of what to do, as my xlsm spreadsheet takes 2 mins ( 120 secs ) to run.

365 Excel running on onedrive
There are 54 pages
No addins
All routines are in Thisworkbook
The first routine reads in data from a stored mdb file on onedrive using con.Provider = "Microsoft.ACE.OLEDB.12.0" ( 9000 lines of data, each of 90 records ) but only takes 3 secs

In running each routine, most are very fast. However there 2 nearly identical routines that take 30 secs and 22 secs, but in them there a lot of "For each sheet" If statements. So I can understand the time taken.

But where can I find what is gobbling up all the remaining time of 68 secs ??

Any advice or guidance

 

[Michael M]

Does it take the same amount of time on a different machine ??

 

[gray_b]

Does it take the same amount of time on a different machine ??

In simple words 'I have no idea'. As only got one pc.

I will see if my android phone can run it

 

[Michael M]

I'd suggest the isuue might be bringing the data from One Drive.
Have you tried saving the mdb to your local drive ?

 

[gray_b]

In simple words 'I have no idea'. As only got one pc.

I will see if my android phone can run it

My android will not run any excel file with vba macros in them.

 

[gray_b]

I have tried

Application.ScreenUpdating = False
code
Application.ScreenUpdating = True

And strangley that increases the time to 140 secs

 

[gray_b]

I'd suggest the isuue might be bringing the data from One Drive.
Have you tried saving the mdb to your local drive ?

The code refers to the mdb file on my hard drive, but it is syn'd with ondrive

con.ConnectionString = _
 "C:\Users\xxxxx\OneDrive\Access\my data.mdb"
 con.Provider = "Microsoft.ACE.OLEDB.12.0"
 con.Open

 

[gray_b]

I borrowed some code from

https://stackoverflow.com/questions/58799143/how-to-speed-up-the-following-excel-vba-code

StartTime = Timer

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

'Your code

Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.AskToUpdateLinks = True
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
    ActiveWindow.View = xlNormalView

'------ END TIMER------
TimeTaken = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "Running time was " & TimeTaken & " (hours, minutes, seconds)"

That produces a time to 138 secs

 

[gray_b]

So not using any of the Microsoft go faster bits of code, my xlsm spreadsheet takes 2 mins ( 120 secs ) to run.