VLookups data updating before WorkBook_Open event.
 

Guys, I have a sheet which has VLOOKUPs onto a different workbook. Also,
within this sheet I have a function which copies the lastest version of a
file down, which I actually want the VLOOKUPs to look at.

The code I have is...

Private Sub Workbook_Open()

On Error Resume Next

Dim fsoFileObject As Object
Dim folFolder As Object

Dim strFileLocation As String
Dim strSC3Location As String

strFileLocation = "C:\SC3"
strSC3Location = "\\ias_01\Statistics\Affinity\Affinity 2004\Affinity Costs
2004\Backup2004.xls"

Set fsoFileObject = CreateObject("Scripting.FileSystemObject")

If Not fsoFileObject.FolderExists(strFileLocation) Then
Set folFolder = fsoFileObject.CreateFolder(strFileLocation)
End If

Application.DisplayAlerts = False

If fsoFileObject.FileExists(strFileLocation & "\Backup2004.xls") Then
Set folFolder = fsoFileObject.DeleteFile(strFileLocation &
"\Backup2004.xls")
End If

Application.DisplayAlerts = True

fsoFileObject.CopyFile strSC3Location, strFileLocation & "\Backup2004.xls",
False

End Sub

The issue I'm getting is that the sheet is asking if I wish to update the
links to get the fresh data before the WorkBook_Open event even fires off!
Any ideas how I could possibly slow this down at all, or how I can re-order
the data updates until WorkBook_Open is complete??

TIA.

VLookups data updating before WorkBook_Open event.
 

You can create a workbook that opens this workbook with links turned off.

Kind of like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book2.xls", UpdateLinks:=0
ThisWorkbook.Close savechanges:=False
End Sub

DaveO wrote:

Guys, I have a sheet which has VLOOKUPs onto a different workbook. Also,
within this sheet I have a function which copies the lastest version of a
file down, which I actually want the VLOOKUPs to look at.

The code I have is...

Private Sub Workbook_Open()

On Error Resume Next

Dim fsoFileObject As Object
Dim folFolder As Object

Dim strFileLocation As String
Dim strSC3Location As String

strFileLocation = "C:\SC3"
strSC3Location = "\\ias_01\Statistics\Affinity\Affinity 2004\Affinity Costs
2004\Backup2004.xls"

Set fsoFileObject = CreateObject("Scripting.FileSystemObject")

If Not fsoFileObject.FolderExists(strFileLocation) Then
Set folFolder = fsoFileObject.CreateFolder(strFileLocation)
End If

Application.DisplayAlerts = False

If fsoFileObject.FileExists(strFileLocation & "\Backup2004.xls") Then
Set folFolder = fsoFileObject.DeleteFile(strFileLocation &
"\Backup2004.xls")
End If

Application.DisplayAlerts = True

fsoFileObject.CopyFile strSC3Location, strFileLocation & "\Backup2004.xls",
False

End Sub

The issue I'm getting is that the sheet is asking if I wish to update the
links to get the fresh data before the WorkBook_Open event even fires off!
Any ideas how I could possibly slow this down at all, or how I can re-order
the data updates until WorkBook_Open is complete??

TIA.

--

Dave Peterson