Run time error 9 when open a workbook

[Klingon]

Hi,

I have run into a problem. I have two files e.g BRE2 master.xlsx & BRE2.xlsx. I open the second file from the first. All variables are correctly set (masterName, SheetNames, sourceFileName, TestVar1), I used the debugger to check this. When Workbooks(sourceFileName).Activate is called I get run time error 9 Index out of range. I don't under stand way? If I "hard set" the sourceFileName "BRE2.xlsx it works. If I have understood Error 9 correctly I have assigned something to that does not exist. Need some help to understand this problem. I'm new to VBA programming.

Sub CaseCopy(masterName, SheetNames, sourceFileName, TestVar1) 
' TestVar1 containes the last cell with a value on row 1 in  master file

 Dim TestVar2 As String 'Assign the value in the last cell on row 1
 Dim r As Long 'Counter
 Dim check As String


' Open Source file
Workbooks(sourceFileName).Activate 'Activate the source file ' [U]Here I get runtime error 9 Index out of range[/U]
' Activate appropriate Sheet
Worksheets(SheetNames).Activate
' Find the last cell with a value on row 1 in source file Assign TestVar2 a value, e.g. "Q3 2017", 2017
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    For r = 3 To 100 Step 1
        If IsEmpty(Worksheets(SheetNames).Cells(1, r).Value) = True Then
            Worksheets(SheetNames).Cells("1,1").Activate
            TestVar2 = Worksheets(SheetNames).Cells(1, r - 1).Value
            Exit For
        End If
    Next

Best regards

Jonas

 

[Klingon]

I should say that it's the path and the file name assigned to the masterName and sourceFileName.

 

[Klingon]

It looks like that was the problem... But way?

 

[Fluff]

What is the value of "sourceFileName"?

 

[Norie]

When referring to a workbook in the Workbooks collection you only need the workbook name, so if sourceFileName contains the name and path that's the problem.

Which workbook are you trying to refer to here?

Also, how are you opening the second workbook?

 

[Klingon]

What is the value of "sourceFileName"?

It Is C:/..../Downloads/.../BRE2.xlsx (It is in a subfolder to download in Win10)

 

[Klingon]

When referring to a workbook in the Workbooks collection you only need the workbook name, so if sourceFileName contains the name and path that's the problem. Ok Understood

Which workbook are you trying to refer to here? I'm refering to the BRE2.xlsx which contains the data that I will use to update the master file with.

Also, how are you opening the second workbook?

Sub OpenFile(sourceFileName)

Sub OpenFile(sourceFileName)


sourceFileName = Application.GetOpenFilename()
Workbooks.Open sourceFileName
sourceFileName = ActiveWorkbook.Name


End Sub

 

[Klingon]

Should look lie this.

Sub OpenFile(sourceFileName)


sourceFileName = Application.GetOpenFilename()
Workbooks.Open sourceFileName
sourceFileName = ActiveWorkbook.Name


End Sub

 

[Norie]

So sourceFileName includes the file path, that's the problem.

 

[Klingon]

Yes that was the problem, thanx for the help.

/Jonas