Link to home
Start Free TrialLog in
Avatar of mike637
mike637Flag for United States of America

asked on

vba identify if workbook is open then set it activeworkbook

Hello Experts,

I have a few workbooks open at the same time while importing data - but I need to find if a specific workbook is open and set it as the activeworkbook.

The only issue is the one workbook has a variable name (after the 21st character).

I need my code to find if workbook:
     If Left(IMPORT_BOOK.Name, 21) = "Daily Sales Store No." is open - then set it as my activeworkbook while I massage some data in it. If it does not exist - then it exits sub.

I am stuck on checking each open workbook.name but trim the name and still set it as active.

(If needed for coding - there is always 4 more characters left and it is in .xls format)

Any assistance would be awesome.
Thank you!
Avatar of Norie
Norie

What is the workbook's fullname?

Also, how have you opened all these workbooks?

PS You don't need a workbook to be active to manipulate data on it.
Avatar of mike637

ASKER

Question: What is the workbook's fullname? = "Daily Sales Store No. 104.xls"
(However, the store number will change depending on which book I have open from 104 to 158)

Question: Also, how have you opened all these workbooks? =

  These are opened manually after I pull them from nightly emails from different sites
Try this, which goes through the open workbooks until it finds the one you want.

If it doesn't it exits the sub.
Dim IMPORT_BOOK As Workbook

      For Each IMPORT_BOOK In Workbooks
             If IMPORT_BOOK.Name Like "Daily Sales Store No*" Then Exit For
      Next IMPORT_BOOK 

      ' check if workbook was found
      ' if not exit sub
      If IMPORT_BOOK Is Nothing then Exit Sub
           
     ' continue with rest of sub 

      IMPORT_BOOK.Active ' not strictly needed

Open in new window

Avatar of mike637

ASKER

Thanks,

But it does not like the line:
  Next IMPORT_BOOK

runtime error 13
Type mismatch
Do you actually have a variable called IMPORT_BOOK?

If you do how is it declared?
Avatar of mike637

ASKER

Hi imnorie,

OK - Let me switch gears and post the whole code and I will explain what is not working and what I would like.

I have my code below - but I do not want to use Windows(2).activate to check if the workbook is open.  I think it there has to be a more stable way to do this.

Now on the code I have posted - After I activate Windows(2) - it crashes there.  Using the activesheets.name is not working - especially since I need to trim it to 25 characters.  I am going about this the wrong way and anything you can offer me will help.

The original file is called"Theoretical Ingred  Usage (Excel) Site 15.xls"  (2 spaces between Ingred and Usage).  This workbook is a variable named file depending on the location.  So I was trying to trim it to make it work. All locations will have the same file name up to the actual Site number.  I used store no. in my first posting to simplify the process, but the issue is the same.


Sub IMPORT()
       
        Dim xDest_Book As Workbook, xSRCE_BOOK As Workbook
        Dim xDest As Worksheet, xSrce As Worksheet
        Dim xFindFirst As Range, xFind_Last As Range, xMAINLINE As Range
       
        Set xDest_Book = ThisWorkbook
       
        Windows(2).Activate
        If ActiveWorkbook.Name = Left(ActiveWorkbook.Name, 25) = "Theoretical Ingred  Usage" Then
            xSRCE_BOOK = ActiveWorkbook
        Else
            MsgBox "Theoretical Ingred Usage form is not open"
           Exit Sub
        End If
       
       
        'Set xDest = xDest_Book.sheets("INVENTORY")
       
        Set xSrce = xSRCE_BOOK.sheets("Sheet1")
       
        xSrce.Activate
             
        Cells.Select
            With Selection
           .MergeCells = False
        End With
           
        Columns("A:A").ColumnWidth = 19
       
        Set xFind_First = xSrce.Range("A2:A52").Find(What:="MAINLINE", lookat:=xlPart).Offset(1)
        Set xFind_Last = Sheet1.Range("A" & xFind_First.Row & ":A52").Find(What:=" ", lookat:=xlPart).Offset(-1)
        Set xMAINLINE = Range(xFind_First, xFind_Last)
       
           
        xMAINLINE.Copy Destination:=xSRCE_BOOK.sheets("Sheet2").Range("A3")
        'I was using this destination for testing only - it goes to other named workbooks that do not change.
       
' I have 5 more sets of ranges in Column "A" after this - but I have to get the first part to work first        
       
End Sub
Did you try the code I posted?

Here it is incorporated into your code and using your variables.

Option Explicit

Sub IMPORT()

Dim xDest_Book As Workbook, xSRCE_BOOK As Workbook
Dim xDest As Worksheet, xSrce As Worksheet
Dim xFind_First As Range, xFind_Last As Range, xMAINLINE As Range

    Set xDest_Book = ThisWorkbook

    For Each xSRCE_BOOK In Workbooks

        If xSRCE_BOOK.Name Like "Theoretical Ingred  Usage*" Then
            ' found workbook so exit loop
            Exit For
        End If

    Next xSRCE_BOOK

     ' check if workbook was found
    If xSRCE_BOOK Is Nothing Then
        MsgBox "Theoretical Ingred Usage form is not open"
        Exit Sub
    End If

    Set xSrce = xSRCE_BOOK.Sheets("Sheet1")

    xSrce.Activate

    With xSrce
        .Cells.MergeCells = False
        .Columns("A:A").ColumnWidth = 19

        Set xFind_First = .Range("A2:A52").Find(What:="MAINLINE", lookat:=xlPart).Offset(1)
        Set xFind_Last = .Range("A" & xFind_First.Row & ":A52").Find(What:=" ", lookat:=xlPart).Offset(-1)

        Set xMAINLINE = .Range(xFind_First, xFind_Last)
    End With
    
    xMAINLINE.Copy Destination:=xSRCE_BOOK.Sheets("Sheet2").Range("A3")

End Sub

Open in new window

Avatar of mike637

ASKER

Ok - put it all in and ran it.

I get error at line 16

      Next xSRCE_BOOK

Run-time error 13

Type mismatch

At least it got past the first section. :)

Michael
Michael

That's as far as you were getting with the first code.

I can't see why that error is happening on that line though.

The variable is declared as a workbook and the code is looping through workbooks.

So how can there be a type mismatch?

Try replacing lines 11-18 with this:
Dim I As Long

....

For I = 1 To Application.Workbooks.Count
      If Workbooks(I).Name Like "Theoretical Ingred  Usage*" Then
           Set xSRCE_BOOK = Workbooks(I)
           Exit For
      End If
Next I

Open in new window

PS This is a stupid question, but are you running this code in Excel?
Avatar of mike637

ASKER

Hello....

OK - my error is at - Set xSRCE_BOOK = Workbooks(I)

I wish I knew why it errors out - I would be relieved.

I am running in Excel 2010. My main workbook is in xlms format.  However, my import file or the one I get that want to pull data from is .xls so it is in [Compatibility Mode] - if that could be causing the problem; but I have never had it before.
I did close and reopen both workbooks and gave it another shot and it errored at that line again.

If you would like I can send both files and you can observe the behavior yourself if that would help in resolving this.

I do appreciate all your help,
Michael
Michael

The different versions shouldn't cause a problem - a workbook is a workbook.

You haven't declared xSRCE_BOOK anywhere else but here where it's declared as  a workbook.
Dim xDest_Book As Workbook, xSRCE_BOOK As Workbook

Open in new window

You can attach both files here if you want.
Avatar of mike637

ASKER

I checked and did not find any occurrences.

I am attaching both the Main book where the code resides in Module 3 and the file for importing.

I am hopeful you will see where the conflict resides.

Thank you very much!
FoodCost-WorkBook-Excel2010.xlsm
Theoretical-Ingred--Usage--Excel.xls
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mike637

ASKER

Wow - I never noticed that.  Now that I opened a new workbook I see it is called MyWorkbook.  Good catch.  I guess that is the problem and I bet I could have you your original code you supplied.

I can not believe this had been the problem all along.

Thank you for checking this out and finding the true issue.

I am very appreciative of you and your time!

Michael
Michael

It's not MyWorkbook, it should be ThisWorkbook

PS You can actually change the name but I wouldn't advise it - look what happened here.:)