Code to make a sheet active

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an active worksheet in a workbook that I need to grab some data off
of. The problem is the name of the workbook that the worksheet resides in
contains a date and time the workbook was created as part of the file name.
I've tried to use wildcards* to acftivate the workbook but that doesn't seem
to work. Here is an example of the code I'm trying to run that isn't working:

Windows(BankNum & "-UserGroupReport" * ".csv").Activate

BankNum comes from user input
-UserGroupReport is static in the name
* represents the part of the file name that contains the date/time

I don't want to make this really complicated. Does anyone have any ideas?
 
You would loop through the workbooks and find the one with a matching name.

You can use something like:

*******************************
Dim wb As Workbook

For Each wb In Workbooks

If wb.Name Like BankNum & "-UserGroupReport* .csv" Then
' work with wb
End If

Next wb
****************************
 
When you open the workbook, set an object variable

Set oWB = Workbooks.Open Filename:= ... etc

and then use that object variable when activating.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Thanks for the reply Tim. Could you tell me what should come after the then
statement?
 
Bob,

here is the code I'm using to open the files.

Public Sub OpenFiles()

On Error GoTo ErrorHandler

Dim FName As Variant
Dim i As Long

FName = Application.GetOpenFilename _
("CSV Files,*.csv", _
MultiSelect:=True)


For i = LBound(FName) To UBound(FName)
Workbooks.Open FName(i)
Application.ScreenUpdating = False

'With Application 'disable the automatic calc feature
'.Calculation = xlManual
'.MaxChange = 0.001
'End With

'Reset Cursor position

Next

ErrorHandler:

End Sub

I require the users to open four files. Three of the four have names that
are generic and easy to use but it's the fourth one I'm having the trouble
with. Looking at the code above that I use to open the files how can I set
the object variable?
 
I would use an array of objects

Public Sub OpenFiles()

On Error GoTo ErrorHandler

Dim FName As Variant
Dim i As Long
Dim aryWBs
FName = Application.GetOpenFilename _
("CSV Files,*.csv", _
MultiSelect:=True)


Redim aryWBs(LBound(FName) To UBound(FName))
For i = LBound(FName) To UBound(FName)
Set aryWBs(i) = Workbooks.Open FName(i)
Application.ScreenUpdating = False

'With Application 'disable the automatic calc feature
'.Calculation = xlManual
'.MaxChange = 0.001
'End With

'Reset Cursor position

Next

ErrorHandler:

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
I appreciate your help Bob but I'm by no means a programmer so I'm getting a
little lost in the process here. I'm getting a compile error for the line:

Set aryWBs(i) = Workbooks.Open FName(i)

Also, I don't see how this code will help me activate the worksheet I
mentioned in my initial post. Opening the files is working great and I can
use 3 of thr 4 right now. I just can't seem to figure our or understand from
everyone who has tried to me how to write the code to activate (and use) the
fourth workbook/sheet.
 
Sorry, that was my fault. The line should read

Set aryWBs(i) = Workbooks.Open(FName(i))


You then activate later without needing to know the name like so

aryWBs(4).Activate

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Thanks Bob!! I got it to work. My followup question would be how do I know
that the file will always be the same number within the array? If I code it
as:

aryWBs(2).Activate - which is what it is right now

How do I know it won't change to 1, 3 or 4?
 
If you know the names of the other 3 workbooks and only this workbook will
be variable, but will contain UserGroupReport in the name, then just set a
reference to that workbook using code like the below


Public Sub OpenFiles()

On Error GoTo ErrorHandler

Dim FName As Variant
Dim i As Long
Dim bk as Workbook
FName = Application.GetOpenFilename _
("CSV Files,*.csv", _
MultiSelect:=True)


For i = LBound(FName) To UBound(FName)
Workbooks.Open FName(i)
if Instr(1,activeWorkbook.Name, _
"UserGroupReport",vbTextcompare) then
set bk = ActiveWorkbook
End if

Next

ErrorHandler:

End Sub


After all 4 workbooks are opened, Bk should be a reference to the
UserGroupReport workbook


If you like the Array approach, you can use code similar to the above to
identify which member of the array is the workbook of interest.
 
Back
Top