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

excel vba trim workbook name and set as activeworkbook

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
mike637
Asked:
mike637
1 Solution
 
FlysterCommented:
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
 
tdlewisCommented:
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
 
SteveCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
mike637Author Commented:
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
 
tdlewisCommented:
Try changing it to:
For Each oBook In Application.Workbooks
0
 
mike637Author Commented:
Ok, I got a little further....

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

Next oBook
0
 
tdlewisCommented:
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
 
mike637Author Commented:
That did the trick.  

Thank you for your Expert advice!

Michael
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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