topic Re: in VBA Forum

Excel Dialog Response

Anonymous — Mon, 05 Mar 2001 09:15:30 GMT

(First posted in MDT5 group)
I am updating MDT5 global variables from an Excel workbook that is fed by
data from a second workbook. When I update the variables (in a VBA
routine), Excel returns a " . . . Do you want to update this workbook with
changes from the other workbook . . .? Yes or No." How do I issue the
needed affirmative response to Excel to keep the program running? Perhaps
this is a better question for the VBA group?

Thanks.
Charlie-

Re: Excel Dialog Response

Anonymous — Mon, 05 Mar 2001 11:40:47 GMT

Hi Charlie,

If you are opening the workbook from VBA then you should check out the
UpdateLinks argument for the .open statement. Given below is an excerpt
from Excel VBA Help:

Syntax

expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password,
WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable,
Notify, Converter, AddToMRU)

expression Required. An expression that returns a Workbooks or RecentFile
object.

FileName Required String. The file name of the workbook to be opened.

UpdateLinks Optional Variant. Specifies the way links in the file are
updated. If this argument is omitted, the user is prompted to specify how
links will be updated. Otherwise, this argument is one of the values listed
in the following table.

Value Meaning
0 Doesn't update any references
1 Updates external references but not remote references
2 Updates remote references but not external references
3 Updates both remote and external references
If Microsoft Excel is opening a file in the WKS, WK1, or WK3 format and the
UpdateLinks argument is 2, Microsoft Excel generates charts from the graphs
attached to the file. If the argument is 0, no charts are created.

Hope this helps.

David

Charlie Carr wrote in message
news:B22FA25B56B138192FB79A0F92C96BA6@in.WebX.maYIadrTaRb...
> (First posted in MDT5 group)
> I am updating MDT5 global variables from an Excel workbook that is fed by
> data from a second workbook. When I update the variables (in a VBA
> routine), Excel returns a " . . . Do you want to update this workbook with
> changes from the other workbook . . .? Yes or No." How do I issue the
> needed affirmative response to Excel to keep the program running? Perhaps
> this is a better question for the VBA group?
>
> Thanks.
> Charlie-
>

Re:

Anonymous — Mon, 05 Mar 2001 12:11:44 GMT

David,
Thanks for the response. Actually, the workbook isbeing opened via :
mcadDoc.UpdateGlobalParameterLink
From a stand alone workbook, this works fine. However when data is called
to that workbook from a remote source, it returns the 'do you want to update
. . .' question. If both files are open, then I don't think the question is
asked. I could use this as a workaround, I s'pose, but I'd rather find a
way with a little more finess. Any other thoughts?

Thanks,
C-

"David Moroni" wrote in message
news:DB22309C4DB270425E91D0DB0364E6B5@in.WebX.maYIadrTaRb...
> Hi Charlie,
>
> If you are opening the workbook from VBA then you should check out the
> UpdateLinks argument for the .open statement. Given below is an excerpt
> from Excel VBA Help:
>
> Syntax
>
> expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password,
> WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable,
> Notify, Converter, AddToMRU)
>
> expression Required. An expression that returns a Workbooks or
RecentFile
> object.
>
> FileName Required String. The file name of the workbook to be opened.
>
> UpdateLinks Optional Variant. Specifies the way links in the file are
> updated. If this argument is omitted, the user is prompted to specify how
> links will be updated. Otherwise, this argument is one of the values
listed
> in the following table.
>
> Value Meaning
> 0 Doesn't update any references
> 1 Updates external references but not remote references
> 2 Updates remote references but not external references
> 3 Updates both remote and external references
> If Microsoft Excel is opening a file in the WKS, WK1, or WK3 format and
the
> UpdateLinks argument is 2, Microsoft Excel generates charts from the
graphs
> attached to the file. If the argument is 0, no charts are created.
>
> Hope this helps.
>
> David
>
> Charlie Carr wrote in message
> news:B22FA25B56B138192FB79A0F92C96BA6@in.WebX.maYIadrTaRb...
> > (First posted in MDT5 group)
> > I am updating MDT5 global variables from an Excel workbook that is fed
by
> > data from a second workbook. When I update the variables (in a VBA
> > routine), Excel returns a " . . . Do you want to update this workbook
with
> > changes from the other workbook . . .? Yes or No." How do I issue the
> > needed affirmative response to Excel to keep the program running?
Perhaps
> > this is a better question for the VBA group?
> >
> > Thanks.
> > Charlie-
> >
>

Re:

Anonymous — Mon, 05 Mar 2001 12:51:08 GMT

Charlie,

Sorry, I'm still pretty new to Autocad, so I'm not familiar with the
mcadDoc.UpdateGlobalParameterLink. I've run into the update links dialog in
other VB projects I've worked on, so I thought the UpdateLink parameter
might apply here too. Good luck with this problem.

David

Charlie Carr wrote in message
news:2656A027977E8FE3644DEB17AF238A16@in.WebX.maYIadrTaRb...
> David,
> Thanks for the response. Actually, the workbook isbeing opened via :
> mcadDoc.UpdateGlobalParameterLink
> From a stand alone workbook, this works fine. However when data is called
> to that workbook from a remote source, it returns the 'do you want to
update
> . . .' question. If both files are open, then I don't think the question
is
> asked. I could use this as a workaround, I s'pose, but I'd rather find a
> way with a little more finess. Any other thoughts?
>
> Thanks,
> C-
>
> "David Moroni" wrote in message
> news:DB22309C4DB270425E91D0DB0364E6B5@in.WebX.maYIadrTaRb...
> > Hi Charlie,
> >
> > If you are opening the workbook from VBA then you should check out
the
> > UpdateLinks argument for the .open statement. Given below is an excerpt
> > from Excel VBA Help:
> >
> > Syntax
> >
> > expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password,
> > WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter,
Editable,
> > Notify, Converter, AddToMRU)
> >
> > expression Required. An expression that returns a Workbooks or
> RecentFile
> > object.
> >
> > FileName Required String. The file name of the workbook to be opened.
> >
> > UpdateLinks Optional Variant. Specifies the way links in the file are
> > updated. If this argument is omitted, the user is prompted to specify
how
> > links will be updated. Otherwise, this argument is one of the values
> listed
> > in the following table.
> >
> > Value Meaning
> > 0 Doesn't update any references
> > 1 Updates external references but not remote references
> > 2 Updates remote references but not external references
> > 3 Updates both remote and external references
> > If Microsoft Excel is opening a file in the WKS, WK1, or WK3 format and
> the
> > UpdateLinks argument is 2, Microsoft Excel generates charts from the
> graphs
> > attached to the file. If the argument is 0, no charts are created.
> >
> > Hope this helps.
> >
> > David
> >
> > Charlie Carr wrote in message
> > news:B22FA25B56B138192FB79A0F92C96BA6@in.WebX.maYIadrTaRb...
> > > (First posted in MDT5 group)
> > > I am updating MDT5 global variables from an Excel workbook that is fed
> by
> > > data from a second workbook. When I update the variables (in a VBA
> > > routine), Excel returns a " . . . Do you want to update this workbook
> with
> > > changes from the other workbook . . .? Yes or No." How do I issue the
> > > needed affirmative response to Excel to keep the program running?
> Perhaps
> > > this is a better question for the VBA group?
> > >
> > > Thanks.
> > > Charlie-
> > >
> >
>

Re:

Anonymous — Mon, 05 Mar 2001 13:29:56 GMT

David,
I'm trying the workaround, and I.m stuck again (I'm very new to VBA, but
been with Autocad for a long time). I tried opening the XL file in the
background to try to prevent the "do you want to update " message from
coming, and it is still there. So I thought I'd try your UpdateLink
parameter, but I'm obviously not getting it right. I've tried a lot of
variations, but it is essentially:
Excel.Workbooks.Open ("filename.xls",3)

I'm missing something in the syntax. The error message I get varies with
what I put in there, but everything turns out to be wrong. Can you give me
a clue?

Thanks,
C-

"David Moroni" wrote in message
news:1731025D98CE5309DB0140242DD30278@in.WebX.maYIadrTaRb...
> Charlie,
>
> Sorry, I'm still pretty new to Autocad, so I'm not familiar with the
> mcadDoc.UpdateGlobalParameterLink. I've run into the update links dialog
in
> other VB projects I've worked on, so I thought the UpdateLink parameter
> might apply here too. Good luck with this problem.
>
> David
>
> Charlie Carr wrote in message
> news:2656A027977E8FE3644DEB17AF238A16@in.WebX.maYIadrTaRb...
> > David,
> > Thanks for the response. Actually, the workbook isbeing opened via :
> > mcadDoc.UpdateGlobalParameterLink
> > From a stand alone workbook, this works fine. However when data is
called
> > to that workbook from a remote source, it returns the 'do you want to
> update
> > . . .' question. If both files are open, then I don't think the
question
> is
> > asked. I could use this as a workaround, I s'pose, but I'd rather find
a
> > way with a little more finess. Any other thoughts?
> >
> > Thanks,
> > C-
> >
> > "David Moroni" wrote in message
> > news:DB22309C4DB270425E91D0DB0364E6B5@in.WebX.maYIadrTaRb...
> > > Hi Charlie,
> > >
> > > If you are opening the workbook from VBA then you should check out
> the
> > > UpdateLinks argument for the .open statement. Given below is an
excerpt
> > > from Excel VBA Help:
> > >
> > > Syntax
> > >
> > > expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password,
> > > WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter,
> Editable,
> > > Notify, Converter, AddToMRU)
> > >
> > > expression Required. An expression that returns a Workbooks or
> > RecentFile
> > > object.
> > >
> > > FileName Required String. The file name of the workbook to be
opened.
> > >
> > > UpdateLinks Optional Variant. Specifies the way links in the file
are
> > > updated. If this argument is omitted, the user is prompted to specify
> how
> > > links will be updated. Otherwise, this argument is one of the values
> > listed
> > > in the following table.
> > >
> > > Value Meaning
> > > 0 Doesn't update any references
> > > 1 Updates external references but not remote references
> > > 2 Updates remote references but not external references
> > > 3 Updates both remote and external references
> > > If Microsoft Excel is opening a file in the WKS, WK1, or WK3 format
and
> > the
> > > UpdateLinks argument is 2, Microsoft Excel generates charts from the
> > graphs
> > > attached to the file. If the argument is 0, no charts are created.
> > >
> > > Hope this helps.
> > >
> > > David
> > >
> > > Charlie Carr wrote in message
> > > news:B22FA25B56B138192FB79A0F92C96BA6@in.WebX.maYIadrTaRb...
> > > > (First posted in MDT5 group)
> > > > I am updating MDT5 global variables from an Excel workbook that is
fed
> > by
> > > > data from a second workbook. When I update the variables (in a VBA
> > > > routine), Excel returns a " . . . Do you want to update this
workbook
> > with
> > > > changes from the other workbook . . .? Yes or No." How do I issue
the
> > > > needed affirmative response to Excel to keep the program running?
> > Perhaps
> > > > this is a better question for the VBA group?
> > > >
> > > > Thanks.
> > > > Charlie-
> > > >
> > >
> >
>

Re:

Anonymous — Mon, 05 Mar 2001 13:57:35 GMT

Charlie,

Try the following to open the workbook

Dim xlApp as New Excel.Application
Dim xlWorkbook as Excel.Workbook

Set xlApp = CreateObject("Excel.Application")

Set xlWorkbook = xlApp.Workbooks.Open(FileName:="filename.xls",
UpdateLinks:=3)

David

Re:

Anonymous — Mon, 05 Mar 2001 14:40:06 GMT

Thanks. I tried it, and I still get the dialog box asking me if I want to
update! The file doesn't actually open in an XL window . . . it must be in
the background, though, because when I try to open the file in XL after
running this, I get a share violation, asking if I want to open it read
only, or notify the other user (myself). Is there a way to completely open
it, then close it all down (including the XL window)?

C-

"David Moroni" wrote in message
news:F97B56229DB70482F060F053BC9544EA@in.WebX.maYIadrTaRb...
> Charlie,
>
> Try the following to open the workbook
>
> Dim xlApp as New Excel.Application
> Dim xlWorkbook as Excel.Workbook
>
> Set xlApp = CreateObject("Excel.Application")
>
> Set xlWorkbook =
xlApp.Workbooks.Open(FileName:="filename.xls",
> UpdateLinks:=3)
>
> David
>

Re:

Anonymous — Mon, 05 Mar 2001 15:27:06 GMT

Charlie, I'm thinking you might have some success with
Excel.Application.DisplayAlerts = False in the code for Excel. This should
disable the message alert. As far as open and shut down, see below.

Public Sub ExcelStart()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
Err.Clear
Set xlApp = CreateObject("Excel.Application")
If Err Then
MsgBox "Could not connect to Microsoft Excel", _
vbCritical + vbOKOnly, sDiaTitle
Err.Clear
Exit Sub
End If
End If

xlApp.DisplayAlerts = False
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlApp.ActiveWorkbook.Sheets("Sheet1")

'

xlApp.Quit

Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing

End Sub

"Charlie Carr" wrote in message
news:E18C80E728346708C9C09E1739DAEBED@in.WebX.maYIadrTaRb...
Thanks. I tried it, and I still get the dialog box asking me if I want to
update! The file doesn't actually open in an XL window . . . it must be in
the background, though, because when I try to open the file in XL after
running this, I get a share violation, asking if I want to open it read
only, or notify the other user (myself). Is there a way to completely open
it, then close it all down (including the XL window)?

C-

"David Moroni" wrote in message
news:F97B56229DB70482F060F053BC9544EA@in.WebX.maYIadrTaRb...
> Charlie,
>
> Try the following to open the workbook
>
> Dim xlApp as New Excel.Application
> Dim xlWorkbook as Excel.Workbook
>
> Set xlApp = CreateObject("Excel.Application")
>
> Set xlWorkbook =
xlApp.Workbooks.Open(FileName:="filename.xls",
> UpdateLinks:=3)
>
> David
>

Re:

Anonymous — Mon, 05 Mar 2001 20:12:34 GMT

Charlie,

You were right that the code opened the workbook in the background.
That is why you got a share violation when you tried to open the workbook
again. Kevin Terry has given you the right approach when it comes to
automating Office apps. Any time you declare an object, you should always
exit or close the object application and then set the object to nothing. I
should have included that in my last post.

David

Re:

Anonymous — Thu, 08 Mar 2001 07:18:44 GMT

Thanks. Kevin.
I'll give this a try when I get the chance. I had a small window of
opportunity to work on this, and I've since been pulled into something else
. . . you know how it goes. Maybe next week?

Thanks, all, for your help.

Charlie-

"Kevin Terry" wrote in message
news:B713A8AE4A78CA0C96935DB6AAE543AF@in.WebX.maYIadrTaRb...
> Charlie, I'm thinking you might have some success with
> Excel.Application.DisplayAlerts = False in the code for Excel. This should
> disable the message alert. As far as open and shut down, see below.
>
> Public Sub ExcelStart()
> Dim xlApp As Excel.Application
> Dim xlBook As Excel.Workbook
> Dim xlSheet As Excel.Worksheet
>
> On Error Resume Next
> Set xlApp = GetObject(, "Excel.Application")
> If Err Then
> Err.Clear
> Set xlApp = CreateObject("Excel.Application")
> If Err Then
> MsgBox "Could not connect to Microsoft Excel", _
> vbCritical + vbOKOnly, sDiaTitle
> Err.Clear
> Exit Sub
> End If
> End If
>
> xlApp.DisplayAlerts = False
> Set xlBook = xlApp.Workbooks.Add
> Set xlSheet = xlApp.ActiveWorkbook.Sheets("Sheet1")
>
> '
>
> xlApp.Quit
>
> Set xlApp = Nothing
> Set xlBook = Nothing
> Set xlSheet = Nothing
>
> End Sub
>
> "Charlie Carr" wrote in message
> news:E18C80E728346708C9C09E1739DAEBED@in.WebX.maYIadrTaRb...
> Thanks. I tried it, and I still get the dialog box asking me if I want to
> update! The file doesn't actually open in an XL window . . . it must be
in
> the background, though, because when I try to open the file in XL after
> running this, I get a share violation, asking if I want to open it read
> only, or notify the other user (myself). Is there a way to completely
open
> it, then close it all down (including the XL window)?
>
> C-
>
> "David Moroni" wrote in message
> news:F97B56229DB70482F060F053BC9544EA@in.WebX.maYIadrTaRb...
> > Charlie,
> >
> > Try the following to open the workbook
> >
> > Dim xlApp as New Excel.Application
> > Dim xlWorkbook as Excel.Workbook
> >
> > Set xlApp = CreateObject("Excel.Application")
> >
> > Set xlWorkbook =
> xlApp.Workbooks.Open(FileName:="filename.xls",
> > UpdateLinks:=3)
> >
> > David
> >
>