sorry.
your constant, cstrDatabaseWB , holds th efull path as well as the name of
the file, ie "C:\Documents and Settings\5YPJB\My
Documents\Excel documents\Sherry project\real1.xls"
However, the workbook name, once its open is just the file name, "real1.xls"
so you need
Workbooks("real1.xls").Activate
and your code would then be
Workbooks("real1.xls").Activate ' probably not necesary
WITH Workbooks("real1.xls").Sheets("Dcidata").Range("$E$6")
.AutoFilter
.AutoFilter field:=5, Criteria1:=lstText
END WITH
of course, this may cause other issues. Note that you don't need to activate
or select ranges in order to use them
"Merlynsdad" wrote:
> I think you're assuming I know more than I know. I'm still a novice at this.
> The top of the module looks like this:
>
> Dim ListIndex As Long
> Dim list As String
> Dim lstText As String
> Dim myRange As Range
> Dim i As Integer
> Const cstrDatabaseWB As String = "C:\Documents and Settings\5YPJB\My
> Documents\Excel documents\Sherry project\real1.xls"
>
>
> On Error GoTo HandleError
> Application.ScreenUpdating = False
>
> ' Select the employee
>
> lstText = lstEmployee.Text
>
> ' Run the DCIData query on the employee from the main workbook
>
> ' wb1.Activate (does this go here???)
> Sheets("Dcidata").Select
> Range("$E$6").Select
> Selection.AutoFilter
> Selection.AutoFilter field:=5, Criteria1:=lstText
>
>
> Could you indicate what you would do a little more clearly, as I'm getting
> lost trying to follow your previous posts. Thanks much.
>
>
>
> "Patrick Molloy" wrote:
>
> > ...and you'll probably want another const for the file path
> >
> > another was would be to dim a couple of variables as workbooks ...
> >
> > eg
> >
> > SET wb1 = workbooks.open(cstrDatabaseWB)
> > SET wb2 = workbooks.open(someother_cstrDatabaseWB)
> >
> > then all you need is
> > wb1.Activate
> > or
> > wb2.Activate
> >
> > seesm easier to me. plus, all you need to close is
> > wb1.close false ' to close without saving
> > or
> > wb2.close true ' to save & close
> >
> >
> >
> >
> >
> > "Patrick Molloy" wrote:
> >
> > > thats becasue the workbook name in the activate method is just the name and
> > > excludes the path
> > >
> > > try
> > > Const cstrDatabaseWB As String = "real1.xls"
> > >
> > >
> > > "Merlynsdad" wrote:
> > >
> > > > No, I'm not closing Book1, I'm just activating it and switching back and
> > > > forth from Book1 to Book 2 as I copy and paste. If I use the actual name of
> > > > the workbook "real1.xls" it works fine. However when I input the constant
> > > > cstrDatabaseWB in the command:
> > > >
> > > > Windows(cstrDatabaseWB).Activate
> > > >
> > > > I get a runtime Error 9, "subscript out of range" error.
> > > >
> > > > The constant is currently defined as:
> > > >
> > > > Const cstrDatabaseWB As String = "C:\Documents and Settings\5YPJB\My
> > > > Documents\Excel documents\Sherry project\real1.xls"
> > > >
> > > > because I'm writing this on my machine, but the constant will eventually end
> > > > up with a network path.
> > > >
> > > >
> > > > "Patrick Molloy" wrote:
> > > >
> > > > > gary's answer is correct if you have two or more workbooks open ... all you
> > > > > need to do is activate the one that to want to use. Are you now saying that
> > > > > when you "switch" to book 2 that you actually CLOSE book1? I' m not clear on
> > > > > thios question.
> > > > >
> > > > >
> > > > > "Merlynsdad" wrote:
> > > > >
> > > > > > That would do it if it were local, but it's out on a network and I'm
> > > > > > referring to it with Constant "cstrDatabaseWB". Using the constant either
> > > > > > with the "" or without doesn't work. I forgot to mention I'm also running the
> > > > > > code in the click event of a listbox.
> > > > > >
> > > > > > "Gary''s Student" wrote:
> > > > > >
> > > > > > > Windows("Book2").Activate
> > > > > > > or
> > > > > > > Windows("Book2.xls").Activate
> > > > > > >
> > > > > > > depending on what is in the Window Title Bar.
> > > > > > > --
> > > > > > > Gary''s Student - gsnu200908
> > > > > > >
> > > > > > >
> > > > > > > "Merlynsdad" wrote:
> > > > > > >
> > > > > > > > I'm switching back and forth between two workbooks. I open Book1 from the
> > > > > > > > ThisWorkbook_Open event in Book2, then switch back to Book2 with a
> > > > > > > > Me.Activate since I'm still in it. How do I switch back to the already opened
> > > > > > > > Book1? Presumably this is also how I would switch back to Book2 while in
> > > > > > > > Book1?
|
Similar Threads
