VBA speed issue, can anyone help?

[TroyMcClure72]

I'm using the code below as part of a subroutine to reformat some Excel data files. When I run the code from an Excel module it takes around 1 to 2 minutes to process the data. When I run it from Access, after creating an excel object etc, it takes 30 minutes +. Can anyone suggest why / how I can speed it up. The ultimate aim is to process these excel data files and import to access tables automatically...

For c = 1 To numcols
For r = 1 To numrows
sht2.Cells(totrows + r + 1, 1).Value = sht1.Cells(1, c + 5).Value
sht2.Cells(totrows + r + 1, 2).Value = sht1.Cells(2, c + 5).Value
sht2.Cells(totrows + r + 1, 3).Value = sht1.Cells(3, c + 5).Value
sht2.Cells(totrows + r + 1, 4).Value = sht1.Cells(4, c + 5).Value
sht2.Cells(totrows + r + 1, 5).Value = sht1.Cells(5, c + 5).Value
sht2.Cells(totrows + r + 1, 6).Value = CDate(sht1.Cells(6, c + 5).Value)
sht2.Cells(totrows + r + 1, 7).Value = CDate(sht1.Cells(7, c + 5).Value)
sht2.Cells(totrows + r + 1, 8).Value = sht1.Cells(r + 8, 1).Value
sht2.Cells(totrows + r + 1, 9).Value = sht1.Cells(r + 8, 2).Value
sht2.Cells(totrows + r + 1, 10).Value = sht1.Cells(r + 8, 3).Value
sht2.Cells(totrows + r + 1, 11).Value = sht1.Cells(r + 8, 4).Value
sht2.Cells(totrows + r + 1, 12).Value = sht1.Cells(r + 8, 5).Value
sht2.Cells(totrows + r + 1, 13).Value = sht1.Cells(r + 8, 6).Value
Next r
totrows = totrows + r - 1
Next c
wk.Names.Add Name:="Data" & i, RefersToR1C1:=sht2.Range("A1").CurrentRegion

 

[PHV]

You may try this:
sht2.Application.ScreenUpdating = False
For c = 1 To numcols
...
Next c
sht2.Application.ScreenUpdating = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TroyMcClure72]

I do have that in already! Sorry just edited out that bit of the code. calculation is turned off too.