Code to make a sheet active

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
****************************

--
Tim Williams
Palo Alto, CA


"hshayh0rn" <[email protected]> wrote in message
news:[email protected]...
> 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?

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)

"hshayh0rn" <[email protected]> wrote in message
news:[email protected]...
> 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?

Thanks for the reply Tim. Could you tell me what should come after the then
statement?

"Tim Williams" wrote:

> 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
> ****************************
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "hshayh0rn" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>

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?

"Bob Phillips" wrote:

> 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)
>
> "hshayh0rn" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>

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)

"hshayh0rn" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
> "Bob Phillips" wrote:
>
> > 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)
> >
> > "hshayh0rn" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> >
> >
> >

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.


"Bob Phillips" wrote:

> 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)
>
> "hshayh0rn" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
> > "Bob Phillips" wrote:
> >
> > > 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)
> > >
> > > "hshayh0rn" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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?
> > >
> > >
> > >
>
>
>

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)

"hshayh0rn" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> "Bob Phillips" wrote:
>
> > 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)
> >
> > "hshayh0rn" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > 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)
> > > >
> > > > "hshayh0rn" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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?
> > > >
> > > >
> > > >
> >
> >
> >

wb.activate


--
Tim Williams
Palo Alto, CA


"hshayh0rn" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the reply Tim. Could you tell me what should come after the
then
> statement?
>
> "Tim Williams" wrote:
>
> > 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
> > ****************************
> >
> > --
> > Tim Williams
> > Palo Alto, CA
> >
> >
> > "hshayh0rn" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> >
> >
> >

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?

"Bob Phillips" wrote:

> 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)
>
> "hshayh0rn" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
> > "Bob Phillips" wrote:
> >
> > > 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)
> > >
> > > "hshayh0rn" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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?
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > 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)
> > > > >
> > > > > "hshayh0rn" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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.

--
Regards,
Tom Ogilvy


"hshayh0rn" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
> "Bob Phillips" wrote:
>
> > 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)
> >
> > "hshayh0rn" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > 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)
> > > >
> > > > "hshayh0rn" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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?
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > 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)
> > > > > >
> > > > > > "hshayh0rn" <[email protected]> wrote in
message
> > > > > > news:[email protected]...
> > > > > > > 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?
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >