J
james
yes
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Bob Barnes
I'm running Access automation to an Excel file that has Links to other Excel
files.
When running code to open that Excel...snippet..
Dim objXLApp As Excel Application
Dim objXLWb As Excel Workbook
DIm M$
.......
M = "C:\ABC.xls"
DoCmd.SetWarnings False '....has no effect to disable these "MsgBoxes" below
Set objXLWb = objXLApp.Workbooks.Open(M)
DoCmd.SetWarnings True
I receive 2 Excel Warnings/ MsgBoxes (not sure what their name is)...
First MsgBox...
This workbook contains links to other data sources.
- To update all linked inforamtion, click Yes.
- To keep the existing information, click No.
Second MsgBox...
This workbook contains one or more links that cannot be updated.
To change the source of links, or to attempt to update values again, click
Edit links.
To open the workbook as is, click Continue.
Using VBA, can I control those Excel Warning/ MsgBoxes to accept for the
First MsgBox..
....."Yes"
...and for the Second MsgBox..."Continue"????
I could just have the User click the 2 responses. It looks like
these are both Modal Boxes, and if so, will they require User
interface, rather than controlling that via code??
TIA - Bob
files.
When running code to open that Excel...snippet..
Dim objXLApp As Excel Application
Dim objXLWb As Excel Workbook
DIm M$
.......
M = "C:\ABC.xls"
DoCmd.SetWarnings False '....has no effect to disable these "MsgBoxes" below
Set objXLWb = objXLApp.Workbooks.Open(M)
DoCmd.SetWarnings True
I receive 2 Excel Warnings/ MsgBoxes (not sure what their name is)...
First MsgBox...
This workbook contains links to other data sources.
- To update all linked inforamtion, click Yes.
- To keep the existing information, click No.
Second MsgBox...
This workbook contains one or more links that cannot be updated.
To change the source of links, or to attempt to update values again, click
Edit links.
To open the workbook as is, click Continue.
Using VBA, can I control those Excel Warning/ MsgBoxes to accept for the
First MsgBox..
....."Yes"
...and for the Second MsgBox..."Continue"????
I could just have the User click the 2 responses. It looks like
these are both Modal Boxes, and if so, will they require User
interface, rather than controlling that via code??
TIA - Bob
C
Clif McIrvin
Hi Bob:
Try Set objXLWb = objXLApp.Workbooks.Open(M),1
where the ,1 is the UpdateLinks parameter to update external but not
remote links. See the Open Method help documentation in the Excell Help
for additional information.
HTH
Try Set objXLWb = objXLApp.Workbooks.Open(M),1
where the ,1 is the UpdateLinks parameter to update external but not
remote links. See the Open Method help documentation in the Excell Help
for additional information.
HTH
B
Bob Barnes
Clif,
Set objXLWb = objXLApp.Workbooks.Open(M),1
gives a Complie error...
Expected: end of statement
Will proceed to read the Excel Help.
Thank you - Bob
Set objXLWb = objXLApp.Workbooks.Open(M),1
gives a Complie error...
Expected: end of statement
Will proceed to read the Excel Help.
Thank you - Bob
B
Bob Barnes
Clif...
It's..
Try Set objXLWb = objXLApp.Workbooks.Open(M,1)
Apparently that accepts both Modal Default values...will verify
w/ the User and post the result here.
Thank you - Bob
It's..
Try Set objXLWb = objXLApp.Workbooks.Open(M,1)
Apparently that accepts both Modal Default values...will verify
w/ the User and post the result here.
Thank you - Bob
H
hor vannara
Bob Barnes said:Clif,
Set objXLWb = objXLApp.Workbooks.Open(M),1
gives a Complie error...
Expected: end of statement
Will proceed to read the Excel Help.
Thank you - Bob
C
Clif McIrvin
Bob, my OE somehow missed your response .. I caught it today 'down the
thread'.
Did you get this working?
After looking again, I think
Set objXLWb = objXLApp.Workbooks.Open(M),1
should be
Set objXLWb = objXLApp.Workbooks.Open(M,1)
or
Set objXLWb = objXLApp.Workbooks.Open M,1
might work as well.
--
Clif
Clif,
Set objXLWb = objXLApp.Workbooks.Open(M),1
gives a Complie error...
Expected: end of statement
Will proceed to read the Excel Help.
Thank you - Bob
thread'.
Did you get this working?
After looking again, I think
Set objXLWb = objXLApp.Workbooks.Open(M),1
should be
Set objXLWb = objXLApp.Workbooks.Open(M,1)
or
Set objXLWb = objXLApp.Workbooks.Open M,1
might work as well.
--
Clif
Clif,
Set objXLWb = objXLApp.Workbooks.Open(M),1
gives a Complie error...
Expected: end of statement
Will proceed to read the Excel Help.
Thank you - Bob
C
Clif McIrvin
Clif McIrvin said:Bob, my OE somehow missed your response .. I caught it today 'down the
thread'.
Did you get this working?
After looking again, I think
Set objXLWb = objXLApp.Workbooks.Open(M),1
should be
Set objXLWb = objXLApp.Workbooks.Open(M,1)
Correction - this next form is incorrect syntax; the Set requires the
( )
B
Bob Barnes
Clif - We've had an ice storm in Kentucky...I haven't been online in almost 3
days.
The Set objXLWb = objXLApp.Workbooks.Open(M,1) DOES prevent the 2 Modal
Boxes from appearing, but NOT sure if it accepts the "Defaults" for those
Modal Boxes.
I'm writing this for a User, and don't know all the Links..yet. NOT sure if
this allows the "Defaults" to run for both Modal Boxes, or essentially does
the "Don't Update" when opening the Excel file manually. Will study more
today, and Post again here.
Thank you - Bob
days.
The Set objXLWb = objXLApp.Workbooks.Open(M,1) DOES prevent the 2 Modal
Boxes from appearing, but NOT sure if it accepts the "Defaults" for those
Modal Boxes.
I'm writing this for a User, and don't know all the Links..yet. NOT sure if
this allows the "Defaults" to run for both Modal Boxes, or essentially does
the "Don't Update" when opening the Excel file manually. Will study more
today, and Post again here.
Thank you - Bob
Clif McIrvin said:Clif McIrvin said:Bob, my OE somehow missed your response .. I caught it today 'down the
thread'.
Did you get this working?
After looking again, I think
Set objXLWb = objXLApp.Workbooks.Open(M),1
should be
Set objXLWb = objXLApp.Workbooks.Open(M,1)
Correction - this next form is incorrect syntax; the Set requires the
( )
C
Clif McIrvin
Bob Barnes said:Clif - We've had an ice storm in Kentucky...I haven't been online in
almost 3
days.
Ugh. I heard a bit on the news from the Louisville area this morning.
You folk aren't used to that kind of weather at all. No fun.
The Set objXLWb = objXLApp.Workbooks.Open(M,1) DOES prevent the 2
Modal
Boxes from appearing, but NOT sure if it accepts the "Defaults" for
those
Modal Boxes.
You'll note from the documentation that ,1 is one of 4 possible values
which I believe determines the behavior of the open.
I'm writing this for a User, and don't know all the Links..yet. NOT
sure if
this allows the "Defaults" to run for both Modal Boxes, or essentially
does
the "Don't Update" when opening the Excel file manually. Will study
more
today, and Post again here.
Thank you - Bob
Good to hear back from you, and you're welcome.
B
Bob Barnes
You'll note from the documentation that ,1 is one of 4 possible values
which I believe determines the behavior of the open. ...Yes, the 1st vlue
updates Links...so I guess it works.
I'm going to meet w/ the User soon and ensure all the Links are updated.
Weather...sometimes we get pretty nasty winter storms here.
Thanks again - Bob
which I believe determines the behavior of the open. ...Yes, the 1st vlue
updates Links...so I guess it works.
I'm going to meet w/ the User soon and ensure all the Links are updated.
Weather...sometimes we get pretty nasty winter storms here.
Thanks again - Bob
C
Clif McIrvin
Bob Barnes said:You'll note from the documentation that ,1 is one of 4 possible values
which I believe determines the behavior of the open. ...Yes, the 1st
vlue
updates Links...so I guess it works.
I'm going to meet w/ the User soon and ensure all the Links are
updated.
Weather...sometimes we get pretty nasty winter storms here.
Thanks again - Bob
You're welcome!
Here's a code snippet from one of my macros -- it's running inside of
Excel, but the idea is the same. In this case, I do *not* want to update
links; and this example uses the named argument syntax to call the open
method. It also includes some crude error handling:
Err.Clear
On Error Resume Next
Workbooks.Open Filename:=QCPath & subPath & BookName, _
UpdateLinks:=0 'do not update
Select Case Err.Number
Case 0 'successful open; return true
IsOpen = True
If Len(showView) > 0 Then
ActiveWorkbook.CustomViews(showView).Show
End If
ActiveWorkbook.Saved = True ' choosing a view did not modify
the workbook
Case 1004 'not found; return false
IsOpen = False
Case Else 'report fatal error
MsgBox Error
End Select
On Error GoTo 0
========= end code
B
Bob Barnes
Thanks for the Excel code Clif.
Have a good weekend - Bob
Have a good weekend - Bob
C
Clif McIrvin
Bob Barnes said:Thanks for the Excel code Clif.
Have a good weekend - Bob
You're welcome. Hope you find it useful!
C
Cav. Salvatore Tabita
Clif McIrvin said:You're welcome!
Here's a code snippet from one of my macros -- it's running inside of
Excel, but the idea is the same. In this case, I do *not* want to update
links; and this example uses the named argument syntax to call the open
method. It also includes some crude error handling:
Err.Clear
On Error Resume Next
Workbooks.Open Filename:=QCPath & subPath & BookName, _
UpdateLinks:=0 'do not update
Select Case Err.Number
Case 0 'successful open; return true
IsOpen = True
If Len(showView) > 0 Then
ActiveWorkbook.CustomViews(showView).Show
End If
ActiveWorkbook.Saved = True ' choosing a view did not modify the
workbook
Case 1004 'not found; return false
IsOpen = False
Case Else 'report fatal error
MsgBox Error
End Select
On Error GoTo 0
========= end code