PDA

View Full Version : Solved: Open file .xls or .xlsm, or xlsm



Rayman
06-10-2011, 03:43 PM
First off all thanks to all members of this forum extremely helpful

This is the problem:

In my code i need to activate a workbook whit a fixed name but variable suffix, it can be xls, xlsm or xlsb

This is the code for xlsm suffix: Workbooks("Rapportini Cantieri.xlsm").Activate

What is the way for activate the workbook with any suffix?

Thanks in advance

GTO
06-10-2011, 05:55 PM
In your thread title, you refer to "Open file," but your code shows activating a workbook. Is the workbook already open, or are we first opening it?

Rayman
06-10-2011, 06:07 PM
In your thread title, you refer to "Open file," but your code shows activating a workbook. Is the workbook already open, or are we first opening it?

Sorry my mistake, the workbook is alredy open i need switch to one workbook to another.
In my code i use the xlsm extension, but my goal is to make my code working with any extension.

( if i saved my workbook with xlsb extension, i need to change all the reference to xlsm in my code)

Thanks for reply

GTO
06-10-2011, 08:00 PM
Presuming you mean any workbook extension, maybe:

Option Explicit

Sub exa()
Dim wb As Workbook
Dim MyBook As Workbook

For Each wb In Workbooks
If wb.Name Like "MyBook.xls*" Then
Set MyBook = wb
Exit For
End If
Next

If Not MyBook Is Nothing Then
MyBook.Activate
End If
End Sub

Rayman
06-11-2011, 09:53 AM
Thanks GTO for reply,
Ill try to integrate your code with mine and il let you now.

Thanks again

Paul_Hossler
06-11-2011, 12:23 PM
you might need to check to make sure it's not an Add In or that it's hidden


For Each wb In Workbooks
If wb.Name Like "MyBook.xls*" Then
'might need these checks also
If Not wb.IsAddin And wb.Windows(1).Visible Then
Set MyBook = wb
Exit For
End If
endf
Next


Paul

Rayman
06-11-2011, 12:38 PM
Thanks Paul,

You are right, check is needed.

Ill assembly your and GTO's code.

Rayman
06-11-2011, 02:54 PM
Ok Paul and GTO, all is done and work perfectly.

Many many thanks

Mario