[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

excel vba trim workbook name and set as activeworkbook

Posted on 2012-09-01
8
Medium Priority
?
652 Views
Last Modified: 2012-09-02
Hello Experts,

I am writing an import task that I will use every week.  However the file I need to copy from has a variable name (after character #37) but I need it to identify this open workbook and set it as my source workbook to import from.  Any assistance would be great.

The full name of a workbook could be:
"Theoretical Ingred Usage (Excel) Site 15.xls"
where the the variable numeric value after the word "Site " would change.  Everything else will be the same.

When I go to identify the workbook to use as an import - I need it to trim the name or some other way to identify it without erroring out.

It will be the only other workbook open.  It will always have the extension ".xls"
The sheet in this workbook will always be "Sheet1" that I use to pull data.

How can I write my code to identify this open workbook as my source workbook to import from with a variable name and set it as a source-workbook to import into a destination-workbook?

I would like to avoid Windows(2).activate and set it from there.

Thank you,
Michael
0
Comment
Question by:mike637
8 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 38358153
If you need to extract the number from your string, you can use this:

=MID(A1,39,LEN(A1)-42)

Just change A1 to your reference cell

Flyster
0
 
LVL 10

Expert Comment

by:tdlewis
ID: 38358181
The following code will find a workbook with a name that starts with "Theoretical Ingred Usage (Excel) Site " and set the variable oSheet to the "Sheet1" worksheet. You can then read the data from Sheet1 of the import workbook using the variable oSheet.
Dim oBook As Workbook
Dim oSheet As Worksheet

    Set oSheet = Nothing
    For Each oBook In Workbooks
        If Left(oBook.Name, 38) = "Theoretical Ingred Usage (Excel) Site " Then
            Set oSheet = oBook.Sheets("Sheet1")
        End If
    Next oBook
    If oSheet Is Nothing Then
        MsgBox "Nothing to import"
        Exit Sub
    End If

Open in new window

0
 
LVL 24

Expert Comment

by:Steve
ID: 38358334
Erm, how did the file get open without it being Dimmed and set an an object in the first place?
If you are to open the file manually, use the FileOpen Dialog and set the workbook object at open. Workbook names change, typo's happen we are all human after all. So to set the object on open without limmiting the name may be better.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:mike637
ID: 38358725
Expert tdlewis,

I am trying the code - but I am getting a "Type mismatch" (runtime error 13) at line

For Each oBook In Workbooks

This code is a module by itself in the Destination Workbook.  I have 2 books open.

Any advice?

Michael
0
 
LVL 10

Expert Comment

by:tdlewis
ID: 38358867
Try changing it to:
For Each oBook In Application.Workbooks
0
 

Author Comment

by:mike637
ID: 38359020
Ok, I got a little further....

Now getting a "Type mismatch" (runtime error 13) at line

Next oBook
0
 
LVL 10

Accepted Solution

by:
tdlewis earned 2000 total points
ID: 38359031
That's baffling. If it made it past the For Each statment, I can't imagine why the corresponding Next statement would fail. At any rate, try changing the definition to this:
Dim oBook As Object
0
 

Author Closing Comment

by:mike637
ID: 38359187
That did the trick.  

Thank you for your Expert advice!

Michael
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question