Activating an opened workbook and assign it as a variable

[TrueTaffy]

Hello everyone,

I am really new to VBA programming. I am attempting to write a code to check if a workbook is already opened, then make it active and then assign a variable name to it before extracting data from the cells and placing in cells of MS Word table. I found a function online which I am attempting to use but I am having trouble in activating the workbook if it's opened.

I have attached the code I am attempting to use below. Thanks for your help.

Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function


Sub ExtractNumbers()

'Declare Excel variables
Dim xlsApp As New Excel.Application ------ For this to work I must set as "New Excel.Application"
Dim xlsDoc As Workbook
Dim xlsSht As Excel.Worksheet
Dim LastRow As Long


'Declare Word variables
Dim wrdApp As Object
Dim wrdDoc As Word.Document
Dim wrdTbl As Object

'Open Excel Workbook and Make "Project Summary" active worksheet


If Not IsFileOpen("F:\Copy of Orders Required.xlsx") Then
xlsApp.Visible = True
Set xlsDoc = Workbooks.Open("F:\Orders.xlsx", UpdateLinks, ReadOnly, False)
Else
Set xlsDoc = Workbooks("Orders.xlsx")------ I would like to do something like this
but Workbooks("Orders.xlsx").Activate does not work either. I am getting Subscript Out of Range Error (Run Time: Error 9)

End If
xlsDoc.Activate
Worksheets("Project Summary").Activate
Set xlsSht = ActiveSheet

 

[Zot]

Once assigned variable to workbook such as
Set xlsDoc = Workbooks("Orders.xlsx")

You do not need to activate it. Just refer to it like xlsDoc.Range("A1").Copy .... whatever.

 

[TrueTaffy]

Once assigned variable to workbook such as
Set xlsDoc = Workbooks("Orders.xlsx")

You do not need to activate it. Just refer to it like xlsDoc.Range("A1").Copy .... whatever.

Thanks for your reply Zot but I can't seem to e able to assign in the first case I am getting the Error 9 Subscript Out of Range. I am not sure what I am doing wrong.

 

[Zot]

Thanks for your reply Zot but I can't seem to e able to assign in the first case I am getting the Error 9 Subscript Out of Range. I am not sure what I am doing wrong.

You checked for different workbook whether it is opened but you were trying to declare another workbook as object?

If Not IsFileOpen("F:\Copy of Orders Required.xlsx") Then
       xlsApp.Visible = True
       Set xlsDoc = Workbooks.Open("F:\Orders.xlsx", UpdateLinks, ReadOnly, False)
   Else
       Set xlsDoc = Workbooks("Orders.xlsx")

 

[TrueTaffy]

You checked for different workbook whether it is opened but you were trying to declare another workbook as object?

If Not IsFileOpen("F:\Copy of Orders Required.xlsx") Then
       xlsApp.Visible = True
       Set xlsDoc = Workbooks.Open("F:\Orders.xlsx", UpdateLinks, ReadOnly, False)
   Else
       Set xlsDoc = Workbooks("Orders.xlsx")

Oops! sorry no. The names should be the same. i.e. it should read

If Not IsFileOpen("F:\Orders.xlsx") Then
       xlsApp.Visible = True
       Set xlsDoc = Workbooks.Open("F:\Orders.xlsx", UpdateLinks, ReadOnly, False)
   Else
       Set xlsDoc = Workbooks("Orders.xlsx")

 

[Zot]

So, does that solve your problem?

 

[TrueTaffy]

No, it didn't. Sorry I should have said.

I've also tried different ways

set xlsDoc = Workbooks("Orders.xlxs")
set xlsDoc = Workbooks("C:\Orders.xlxs")
Workbooks("Orders.xlxs").Activate
Workbooks("C:\Orders.xlxs").Activate

But none of these worked once the file was opened.

 

[Zot]

No, it didn't. Sorry I should have said.

I've also tried different ways

set xlsDoc = Workbooks("Orders.xlxs")
set xlsDoc = Workbooks("C:\Orders.xlxs")
Workbooks("Orders.xlxs").Activate
Workbooks("C:\Orders.xlxs").Activate

But none of these worked once the file was opened.

What do you expect to happen?
Set statement will just assign object so that you can just call it to be used without activating it.

The Activate will make that particular workbook selected or active. I think it should be on top of all opened files. That's all.

 

[TrueTaffy]

well there are two issues. I wanted to make the code robust that in the event the user had another workbook opened, the correct one would be active. Secondly, as i have it, the code opened multiple copies of the same file if it encountered an issue. I have been working on it and I'm thinking to ensure the code includes lines to close the file even if it encounters an issue. So the user will have to run again after addressing the issue. This might be a clunky work around but I guess I will improve it as I get better at programming.