(Disable macro's while) using the GetObject

M

Max Potters

Hi all,

I have a piece of code which opens an excel file ("Optellen.xls") (this file
has code in it, also in the Workbook_Open). When I run the code which opens
Optellen.xls, the debugger debugs in Optellen.xls???. How is this possible,
does it run the VB code in Opt...xls as well???

QUESTION: How can I make sure, that my code just opens Optellen.xls, but
(probably thats the problem) without enabling it's macro's?
Here's my code:

Public Sub BatchNames()
'
Dim Wrb As Excel.Workbook
'
Set Wrb = GetObject("C:\Documents and
Settings\Max\Desktop\Rekenprogramma\Optellen.xls")
'
Wrb.Unprotect (my password)
'
filepath = "C:\Documents and Settings\Max\Desktop\temp"
Filename = "namen.xls"
sheetname = "Sheet1"

Wrb.Sheets("Leraar").Range("A2:A18").ClearContents
'
For n = 2 To 36
Wrb.Sheets("Leraar").Cells(n, 2).Value = 1
Wrb.Sheets("Leraar").Cells(n, 4).Value = 0
Next n
'
For n = 2 To 15
Strg = "'" & filepath & "\[" & Filename & "]" _
& sheetname & "'!" & "r" & n - 1 & "c1"
MsgBox "Strg = " & Strg 'just a test
Wrb.Sheets("Leraar").Cells(n, 1).Value = ExecuteExcel4Macro(Strg)
Next n
'
Wrb.Save
'
Wrb.Close
'
Set Wrb = Nothing
'
End Sub
 
T

Tom Ogilvy

Why are you using getobject to open an excel file. Just use workbooks.Open
and disable events before opening.
 
M

Max Potters

Tom, I considered that. But can I open a file with a huge path like I have.

For example: workbooks.Open("C:\Documents and
Settings\max\Desktop\namen.xls")???

And, second of all, how do I disable it's events? I don't want to see the
Enable/Disable macro message when it starts, i just want to disable them

how do I do this?

Thanks (already)
Max
Tom Ogilvy said:
Why are you using getobject to open an excel file. Just use workbooks.Open
and disable events before opening.

--
Regards,
Tom Ogilvy

Max Potters said:
Hi all,

I have a piece of code which opens an excel file ("Optellen.xls") (this file
has code in it, also in the Workbook_Open). When I run the code which opens
Optellen.xls, the debugger debugs in Optellen.xls???. How is this possible,
does it run the VB code in Opt...xls as well???

QUESTION: How can I make sure, that my code just opens Optellen.xls, but
(probably thats the problem) without enabling it's macro's?
Here's my code:

Public Sub BatchNames()
'
Dim Wrb As Excel.Workbook
'
Set Wrb = GetObject("C:\Documents and
Settings\Max\Desktop\Rekenprogramma\Optellen.xls")
'
Wrb.Unprotect (my password)
'
filepath = "C:\Documents and Settings\Max\Desktop\temp"
Filename = "namen.xls"
sheetname = "Sheet1"

Wrb.Sheets("Leraar").Range("A2:A18").ClearContents
'
For n = 2 To 36
Wrb.Sheets("Leraar").Cells(n, 2).Value = 1
Wrb.Sheets("Leraar").Cells(n, 4).Value = 0
Next n
'
For n = 2 To 15
Strg = "'" & filepath & "\[" & Filename & "]" _
& sheetname & "'!" & "r" & n - 1 & "c1"
MsgBox "Strg = " & Strg 'just a test
Wrb.Sheets("Leraar").Cells(n, 1).Value = ExecuteExcel4Macro(Strg)
Next n
'
Wrb.Save
'
Wrb.Close
'
Set Wrb = Nothing
'
End Sub
Click to expand...
Click to expand...
 
T

Tom Ogilvy

Application.EnableEvents = False
set wkbk = workbooks.Open( _
"C:\Documents and Settings\max\Desktop\namen.xls")
Application.EnableEvents = True
msgbox wkbk.Name & " has been opened"

this doesn't disable macros, but it keeps the workbook_open event from
running which is usually what is desired.

--
Regards,
Tom Ogilvy



Max Potters said:
Tom, I considered that. But can I open a file with a huge path like I have.

For example: workbooks.Open("C:\Documents and
Settings\max\Desktop\namen.xls")???

And, second of all, how do I disable it's events? I don't want to see the
Enable/Disable macro message when it starts, i just want to disable them

how do I do this?

Thanks (already)
Max
Tom Ogilvy said:
Why are you using getobject to open an excel file. Just use workbooks.Open
and disable events before opening.

--
Regards,
Tom Ogilvy

Max Potters said:
Hi all,

I have a piece of code which opens an excel file ("Optellen.xls")
Click to expand...
(this
file
has code in it, also in the Workbook_Open). When I run the code which opens
Optellen.xls, the debugger debugs in Optellen.xls???. How is this possible,
does it run the VB code in Opt...xls as well???

QUESTION: How can I make sure, that my code just opens Optellen.xls, but
(probably thats the problem) without enabling it's macro's?
Here's my code:

Public Sub BatchNames()
'
Dim Wrb As Excel.Workbook
'
Set Wrb = GetObject("C:\Documents and
Settings\Max\Desktop\Rekenprogramma\Optellen.xls")
'
Wrb.Unprotect (my password)
'
filepath = "C:\Documents and Settings\Max\Desktop\temp"
Filename = "namen.xls"
sheetname = "Sheet1"

Wrb.Sheets("Leraar").Range("A2:A18").ClearContents
'
For n = 2 To 36
Wrb.Sheets("Leraar").Cells(n, 2).Value = 1
Wrb.Sheets("Leraar").Cells(n, 4).Value = 0
Next n
'
For n = 2 To 15
Strg = "'" & filepath & "\[" & Filename & "]" _
& sheetname & "'!" & "r" & n - 1 & "c1"
MsgBox "Strg = " & Strg 'just a test
Wrb.Sheets("Leraar").Cells(n, 1).Value = ExecuteExcel4Macro(Strg)
Next n
'
Wrb.Save
'
Wrb.Close
'
Set Wrb = Nothing
'
End Sub
Click to expand...
Click to expand...
Click to expand...
 
M

Max Potters

Thank you Tom
Tom Ogilvy said:
Application.EnableEvents = False
set wkbk = workbooks.Open( _
"C:\Documents and Settings\max\Desktop\namen.xls")
Application.EnableEvents = True
msgbox wkbk.Name & " has been opened"

this doesn't disable macros, but it keeps the workbook_open event from
running which is usually what is desired.

--
Regards,
Tom Ogilvy



Max Potters said:
Tom, I considered that. But can I open a file with a huge path like I have.

For example: workbooks.Open("C:\Documents and
Settings\max\Desktop\namen.xls")???

And, second of all, how do I disable it's events? I don't want to see the
Enable/Disable macro message when it starts, i just want to disable them

how do I do this?

Thanks (already)
Max
Tom Ogilvy said:
Why are you using getobject to open an excel file. Just use workbooks.Open
and disable events before opening.

--
Regards,
Tom Ogilvy

Hi all,

I have a piece of code which opens an excel file ("Optellen.xls") (this
file
has code in it, also in the Workbook_Open). When I run the code which
opens
Optellen.xls, the debugger debugs in Optellen.xls???. How is this
possible,
does it run the VB code in Opt...xls as well???

QUESTION: How can I make sure, that my code just opens Optellen.xls, but
(probably thats the problem) without enabling it's macro's?
Here's my code:

Public Sub BatchNames()
'
Dim Wrb As Excel.Workbook
'
Set Wrb = GetObject("C:\Documents and
Settings\Max\Desktop\Rekenprogramma\Optellen.xls")
'
Wrb.Unprotect (my password)
'
filepath = "C:\Documents and Settings\Max\Desktop\temp"
Filename = "namen.xls"
sheetname = "Sheet1"

Wrb.Sheets("Leraar").Range("A2:A18").ClearContents
'
For n = 2 To 36
Wrb.Sheets("Leraar").Cells(n, 2).Value = 1
Wrb.Sheets("Leraar").Cells(n, 4).Value = 0
Next n
'
For n = 2 To 15
Strg = "'" & filepath & "\[" & Filename & "]" _
& sheetname & "'!" & "r" & n - 1 & "c1"
MsgBox "Strg = " & Strg 'just a test
Wrb.Sheets("Leraar").Cells(n, 1).Value = ExecuteExcel4Macro(Strg)
Next n
'
Wrb.Save
'
Wrb.Close
'
Set Wrb = Nothing
'
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Executeexcel4Macro?? (Dave, I need some help) 3
PUT data in closed workbook 5

Top