• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 481
  • Last Modified:

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!
0
mike637
Asked:
mike637
  • 8
  • 7
1 Solution
 
NorieCommented:
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.
0
 
mike637Author Commented:
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
0
 
NorieCommented:
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

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mike637Author Commented:
Thanks,

But it does not like the line:
  Next IMPORT_BOOK

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

If you do how is it declared?
0
 
mike637Author Commented:
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
0
 
NorieCommented:
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

0
 
mike637Author Commented:
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
0
 
NorieCommented:
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?
0
 
mike637Author Commented:
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
0
 
NorieCommented:
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.
0
 
mike637Author Commented:
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
0
 
NorieCommented:
The problem is that for some reason the ThisWorkbook module in the FoodCost Workbook has been renamed to 'Workbook'.

So in the code when a variable is declared as Workbook it is actually declared as ThisWorkbook

That's why there's a type mismatch as the other workbook isn't a ThisWorkbook.

Hope that makes some sort of sense.

Anyway, it's easy to fix just rename the module with the name Workbook to ThisWorkbook.

You'll find that module in the Microsoft Excel Objects folder in the Project Explorer of the VBE, it's the bottom module in that folder.

By the way, the name of the 2nd workbook doesn't seem to match what you posted.

It has -  (dashes) instead of spaces.

They might have been added to the file you uploaded but you should check the name in the code tallys with the filename including any spaces.
0
 
mike637Author Commented:
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
0
 
NorieCommented:
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.:)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now