vba code does not run

  • Thread starter Thread starter bobh
  • Start date Start date
B

bobh

Hi All,
I have this vba code in an Access2003 application and the line

.ActiveWorkbook.RefreshAll

does not work 100% of the time and I don't know why. Everything else
works cell A3 gets updated and the workbook gets saved in it's proper
place. Each workbook has a query in this Access db that is the
workbooks data source. If I open each workbook and do a 'RefreshAll'
it works every time without any problems. Defeats automation if I
have
to open each workbook(currently there are 35 different workbooks in
this loop) and check it be sure that the data did refresh.
So, any ideas or insights as to why the 'RefreshAll' when sent from
Access vba does/would not work 100% of the time.......

I know this is Access vba and I have posted this in the Access forum
But I'm looking for any ideas or insight from an Excel perspective as
to why the Excel workbook would not refresh.

Do Until MyRec.EOF
With objExcel
.Visible = False
.DisplayAlerts = False
.Workbooks.Open strOPathNme & strOFileNme
.Range("A3").Select
.ActiveCell.FormulaR1C1 = strLongNmeDte & ", " &
Left(Me.bxMthFor, 4)
.ActiveWorkbook.RefreshAll
.ActiveWorkbook.SaveAs FileName:=strSPathNme & strSFileNme
.ActiveWorkbook.Close savechanges:=False 'this is the
template
file so don't save the changes
.Workbooks.Close
DoEvents
End With
MyRec.MoveNext
 
Hi All,
I have this vba code in an Access2003 application and the line

 .ActiveWorkbook.RefreshAll

does not work 100% of the time and I don't know why. Everything else
works cell A3 gets updated and the workbook gets saved in it's proper
place. Each workbook has a query in this Access db that is the
workbooks data source. If I open each workbook and do a 'RefreshAll'
it works every time without any problems. Defeats automation if I
have
to open each workbook(currently there are 35 different workbooks in
this loop) and check it be sure that the data did refresh.
So, any ideas or insights as to why the 'RefreshAll'  when sent from
Access vba does/would not work 100% of the time.......

I know this is Access vba and I have posted this in the Access forum
But I'm looking for any ideas or insight from an Excel perspective as
to why the Excel workbook would not refresh.

Do Until MyRec.EOF
   With objExcel
      .Visible = False
      .DisplayAlerts = False
      .Workbooks.Open strOPathNme & strOFileNme
      .Range("A3").Select
      .ActiveCell.FormulaR1C1 = strLongNmeDte & ", " &
Left(Me.bxMthFor, 4)
      .ActiveWorkbook.RefreshAll
      .ActiveWorkbook.SaveAs FileName:=strSPathNme & strSFileNme
      .ActiveWorkbook.Close savechanges:=False   'this is the
template
file so don't save the changes
      .Workbooks.Close
      DoEvents
   End With
MyRec.MoveNext
Click to expand...

In searching around this forum I've come across this several times but
I believe at least this is coded to work on a spreadsheet. I'm not
that great at Excel macros but if I changed my process to call a macro
instead what would the construct be so the macro refreshs the entire
workbook and not just a worksheet

worksheet construct
With ActiveSheet.QueryTables(1)
.BackgroundRefresh = False
.Refresh
End With

workbook construct = ???

thanks
bobh.
 
In searching around this forum I've come across this several times but
I believe at least this is coded to work on a spreadsheet. I'm not
that great at Excel macros but if I changed my process to call a macro
instead what would the construct be so the macro refreshs the entire
workbook and not just a worksheet

worksheet construct
With ActiveSheet.QueryTables(1)
   .BackgroundRefresh = False
   .Refresh
End With

workbook construct = ???

thanks
bobh.- Hide quoted text -

- Show quoted text -
Click to expand...

I have this macro code that I'm trying to use in Excel 2007 and it
does not work, it errors on the FOR I = 1 line with
"Runtime Error 438 - Object doesn't support this property or method"
I don't know Excel that well so, what's wrong with the code???

Dim i
For i = 1 To ActiveWorkbook.QueryTables.Count
ActiveWorkbook.QueryTables(i).Refresh BackgroundQuery:=False
Next
ActiveWorkbook.RefreshAll

thanks
bobh.
 
I have this macro code that I'm trying to use in Excel 2007 and it

does not work, it errors on the FOR I = 1 line with

"Runtime Error 438 - Object doesn't support this property or method"

I don't know Excel that well so, what's wrong with the code???



Dim i

For i = 1 To ActiveWorkbook.QueryTables.Count

ActiveWorkbook.QueryTables(i).Refresh BackgroundQuery:=False

Next

ActiveWorkbook.RefreshAll



thanks

bobh.
Click to expand...

I think the QueryTables object is a member of the Sheet object and not Workbook object. So, you'd need to cycle through all the sheets in your workbook and refresh the QueryTable objects that way.
 
Back
Top