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

On Nov 20, 9:19*am, bobh wrote:
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

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.

On Nov 20, 1:58*pm, bobh wrote:
On Nov 20, 9:19*am, bobh wrote:





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

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 -

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.

On Wednesday, November 21, 2012 6:51:53 AM UTC+10, bobh wrote:
On Nov 20, 1:58*pm, bobh wrote:

On Nov 20, 9:19*am, bobh wrote:











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



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 -



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 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.