mike637
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!
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!
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
(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.
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
ASKER
Thanks,
But it does not like the line:
Next IMPORT_BOOK
runtime error 13
Type mismatch
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?
If you do how is it declared?
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("INVENTO RY")
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:="MA INLINE", 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.sh eets("Shee t2").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
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("INVENTO
Set xSrce = xSRCE_BOOK.sheets("Sheet1"
xSrce.Activate
Cells.Select
With Selection
.MergeCells = False
End With
Columns("A:A").ColumnWidth
Set xFind_First = xSrce.Range("A2:A52").Find
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.sh
'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.
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
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
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:
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
PS This is a stupid question, but are you running this code in Excel?
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
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.
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
You can attach both files here if you want.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.:)
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.:)
Also, how have you opened all these workbooks?
PS You don't need a workbook to be active to manipulate data on it.