getObject disable update links or choose don't update

[316johniam]

heres the code:

Set wb = GetObject("F:\Funding Team\S5\UnfundedTemplate\Unfunded Template.xls")

Application.DisplayAlerts = False

This works, however, it takes a long time and seems to be updating the links anyway.

[lucas]

Hi John,
Sorry and it may be just me but I'm not following your thought process. You say it works....what does that mean? It sets wb =....... and sets DisplayAlerts=False...

I assume from this and your other post that your trying to open a different workbook. Could you please give some idea ( a layout per se) of what your trying to do. An example of what you would like the results to be would help also....waiting to assist but need more info please.

[316johniam]

Hey Steve,

Yes, from workbook A, I'm using Getobject to open workbook B.

When workbook B is opened, I don't want to update Links

I've set DisplayAlerts=False..., but the Links are still updated. I know this because the status bar on the bottom of excel displays the Links update status bar.

[lucas]

More questions John,
first what are you doing with workbook B when you open it?
Retrieving data can be done from a closed workbook without the links updating I think(don't quote me)
why would you not want the links to update?
Basically the more information and the clearer you can be about what your trying to do will help members address your problem..don't hesitate to post your code or workbook.

[316johniam]

Private Sub CommandButton4_Click()
'This will open the workbook, get the value, then close the workbook

Dim vRequiredValue As Variant
Dim wb As Workbook
Set wb = GetObject("F:\Funding Team\123 Template.xls")
Application.DisplayAlerts = False

vRequiredValue = wb.Sheets("My").Range("B12:E14").ClearContents
wb.Close (True)

'You must add a reference to the Microsoft outlook Library
Dim OutApp As Object
Dim OutMail As Object

Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Hi!" & vbNewLine & vbNewLine & _
"" & vbNewLine & _
"" & vbNewLine & _
"" & vbNewLine & _
"" With OutMail
.To = "123@mail.com (123@mail.com)"
.CC = ""
.BCC = ""
.Subject = "No unfunded issues or update for team AC"
.Body = strbody
.display 'or use .send
SendKeys "%{s}", True
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

[lucas]

Step by step:
your opening the 123 template.xls
Set wb = GetObject("F:\Funding Team\123 Template.xls")


this part I don't understand...your setting a value and clearing contents.
all on a sheet named "My" in a given range of the 123 Template.xls
vRequiredValue = wb.Sheets("My").Range("B12:E14").ClearContents
what do you want to happen with this second piece of code?

[lucas]

Another question is why your using getobject instead of Workbooks.Open

Set wb = Workbooks.Open("F:\Funding Team\123 Template.xls", True, True)

[lucas]

code for copying a range from a closed workbook to the active workbook. Pretty well commented but if you have questions.


Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating

'Make path selections below ..workbook to open
Set wb = Workbooks.Open("f:\Temp\Social Club.xls", True, True)
' Set wb = Workbooks.Open(ActiveWorkbook.Path & "\Social Club.xls")
' the sheet in this workbook to copy to
With ThisWorkbook.Worksheets("Final Results")
' read data from the source workbook
'the range to copy to in this workbook-name of sheet to copy FROM-range in closed workbook to copy
.Range("B8").Formula = wb.Worksheets("RESULTS").Range("B7").Formula
.Range("R8").Formula = wb.Worksheets("RESULTS").Range("R7").Formula

End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub


It looks like your trying to open a closed workbook, retrieve data, send the retrieved info in an email.....sound about right?

[316johniam]

I'm trying to update a closed workbook. The update is only to clear a range of cells.

I used getobject, because workbooks open will not work.
Set wb = Workbooks.Open("F:\Funding Team\123 Template.xls", True, True)

This file is closed.


I will try your code. Thanks

[316johniam]

It worked great. Thanks Lucas, I appreciate your help.

[lucas]

Good News John, if you run into more problems post your questions.....be sure to mark your threads solved if you have gotten a solution.