XL 2002 Refering to a workbook

[vaneagle]

howdy,

How do I change to another workbook which is open in excel, yet the name will be different when I receive the file? I want to use code to reference between the two... just not sure how or even if it can be done....

Dim filename As String
    Dim wb As Workbook
    Dim wb2 As Workbook
    
    Set wb = ThisWorkbook
    
    filename = ?????
    
    Windows(filename).Activate
    Set wb2 = ActiveWorkbook

So basically wb will already be active, before I open up the second workbook (wb2). It then needs to refer to the second workbook via Windows(filename).Activate... which i then proceed to extract out some data...

Any ideas?

 

[TonyJollans]

Hi vaneagle,

A couple of questions here.

I'm not clear: do you, or don't you, know the filename of the second workbook? If not, how do you open it?

Secondly, why do you need to activate the second workbook? You shouldn't need to do it, but the process of opening will do it automatically.

You should be able to do something like ..

[blue]Dim wb  As Workbook
Dim wb2 As Workbook

Set wb = ThisWorkbook
Set wb2 = Workbooks.Open(Filename)[/blue]

But if you don't know the filename, I don't know how you're going to open it.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[vaneagle]

Sorry Tony,

I do know the name of the workbook. Thing is that the workbook name will chance everytime I receive it via e-mail. Reson being as that part of the filename contains a date. So I was wondering is it possible once you have the file open within excel, to reference to that file. Before I just had a dialog box where I put in the name of the file, and then ran my code. I guess I am just wondering is there another way to go about refering to the file without me going and puting the file name in the dialog box?

I have expanded my code a fraction more so you can see what currently happens at the start....

    Dim filename As String, defaultname As String
    Dim wb As Workbook
    Dim wb2 As Workbook
    
    Set wb = ThisWorkbook
    
    defaultname = "Data 12th Sept 2004.xls"
    
    filename = InputBox("Enter file name", "Filename", defaultname)
    
    Windows(filename).Activate
    Set wb2 = ActiveWorkbook
    Worksheets("Sheet1").Visible = True
    Worksheets("Sheet1").Select
    Range("AJ1:AT15").Select

Regards,
Paul

 

[SkipVought]

Hi,

Try something like this

set Mywb = ThisWorkbook
for each wb in workbooks
  with wb
    if .name <> Mywb.name then
      msgbox .name
    end if
  end with
next

you ought to be able to figger out which is which.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]