Mike Blackman
Well-known Member
- Joined
- Jun 8, 2007
- Messages
- 2,494
Hi All,
I have a workbook that sucks data in from Access, I was using the following code 3 times to refresh the tables;
Worked fine, Jon von der Heyden suggested that I use;
In its stead, This seems to work much much quicker than my previos code but the rest of my code does not run after the refresh.
I know the obvious answer is to use the querytable.Refresh but can't understand why the Refreshall would block my code. I don't get any errors its simply that it seems to ignore my code.
Can anybody shed any light on this?
My full code is
I have a workbook that sucks data in from Access, I was using the following code 3 times to refresh the tables;
Code:
.Range("A1").QueryTable.Refresh BackgroundQuery:=False
Worked fine, Jon von der Heyden suggested that I use;
Code:
Activeworkbook.Refreshall
In its stead, This seems to work much much quicker than my previos code but the rest of my code does not run after the refresh.
I know the obvious answer is to use the querytable.Refresh but can't understand why the Refreshall would block my code. I don't get any errors its simply that it seems to ignore my code.
Can anybody shed any light on this?
My full code is
Code:
Sub Data_Integration()
Dim lRow As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
ActiveWorkbook.RefreshAll
lRow = Sheets("OCC Data").Range("A" & Rows.count).End(xlUp).Row
With Sheets("OCC Data")
For i = 2 To lRow
If .Cells(i, 32) = "00/00/0000" Then
.Cells(i, 32).ClearContents
.Cells(i, 33).ClearContents
End If
Next i
.Columns("AF").TextToColumns Destination:=.Range("AF1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
.Columns("AG").TextToColumns Destination:=.Range("AG1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
End With
With Sheets("Cost Data")
.Columns("E").TextToColumns Destination:=.Range("E1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
End With
With Sheets("Timesheet Data")
.Columns("I").TextToColumns Destination:=.Range("I1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
.Columns("J").TextToColumns Destination:=.Range("J1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
.Columns("K").TextToColumns Destination:=.Range("K1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
End With