curWorkbook.Activate activates Personal.xlsb

[doncarp]

I have been processing my spreadsheets manually for at least 6 years because I did not take the time to learn how to make a macro do the work for me. Now I am getting into it and am having a measure of success. In one of the steps I use a VLOOKUP function to populate in columns C and D from a table in SortLookup.xlsx, based on the Item ID in column B. Inexplicably, sometimes the function works without opening SortLookup.xlsx and somtimes it does not. However, it always works if I have SortLookup.xlsx open.

To be safe in my macro I want to open SortLookup.xlsx, then proceed with my macro. Here is the part of my code giving me trouble.

Dim curWorkbook As Workbook
Set curWorkbook = ActiveWorkbook
Workbooks.Open Filename:="SortLookup.xlsx"
curWorkbook.Activate

The last line activates Personal.xlsb rather than the worksheet I was working on. What am I doing wrong?

 

[JoeMo]

A couple of things:
If a file is not open then the Filename argument in the WorkBooks.Open line must include the full path to the file, not just the file's name.

You should test to see if the file is already open. The function included in the code below will do that for you. In the test sub, fill in the full path where indicated:

Sub Test()
Dim curWorkbook As Workbook
If Not WorkbookOpen("SortLookup.xlsx") Then
    Workbooks.Open Filename:="Need full path to SortLookup.xlsx here"
Else
    Set curWorkbook = Workbooks("SortLookup.xlsx")
    curWorkbook.Activate
End If
End Sub
Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

 

[doncarp]

JoeMo, thank you for your reply. I accept your advice for checking to see if SortLookup.xlsx is already open, however that is not the issue in my original question. I guess I did not do a good job of explaining.

I export data from an estimating program to Excel. The worksheet it creates defaults to "Sheet (Next Number)" If it is the first export since I opened Excel, it will be "Sheet 1.xlsx" We will assume it is Sheet 1 that I am working in for this discussion.

I want to set Sheet 1 as the current workbook, open SortLookup.xlsx, then go back to Sheet 1. When I step through the lines of code below, the last line activates Personal.xlsb, not Sheet 1.xlsx.

Dim curWorkbook As Workbook
Set curWorkbook = ActiveWorkbook
Workbooks.Open Filename:="SortLookup.xlsx"
curWorkbook.Activate

 

[JoeMo]

JoeMo, thank you for your reply. I accept your advice for checking to see if SortLookup.xlsx is already open, however that is not the issue in my original question. I guess I did not do a good job of explaining.

I export data from an estimating program to Excel. The worksheet it creates defaults to "Sheet (Next Number)" If it is the first export since I opened Excel, it will be "Sheet 1.xlsx" We will assume it is Sheet 1 that I am working in for this discussion.

I want to set Sheet 1 as the current workbook, open SortLookup.xlsx, then go back to Sheet 1. When I step through the lines of code below, the last line activates Personal.xlsb, not Sheet 1.xlsx.

Dim curWorkbook As Workbook
Set curWorkbook = ActiveWorkbook
Workbooks.Open Filename:="SortLookup.xlsx"
curWorkbook.Activate

Is "Sheet 1.xlsx" the active workbook when the line:
Set curWorkbook = ActiveWorkbook
is executed?

 

[doncarp]

Yes it is.