Changing the active workbook

[SP]

I have a workbook called TicketReport.xls which hold the code, I need
to open a workbook that is sent to me and reformat it.
The code starts off ok, it opens the specified workbook but then it
makes the original workbook active. This is the code I have so far:

Sub CreateReport()
Dim fileToOpen As String

'Choose workbook to open
fileToOpen = Application.GetOpenFilename("Text Files (*.txt),*.txt,
Excel Files (*.xls), *.xls", 2)

Workbooks.Open fileToOpen
Workbooks(fileToOpen).Activate
Worksheets("Custom Report").Activate

'Added MsgBox to verify which Workbook & worksheet are active
MsgBox "The name of the active Workbook is " & ActiveWorkbook.Name
MsgBox "The name of the active Sheet is " & ActiveSheet.Name


'Delete "Category" column
myVar = WorksheetFunction.Match("Category", Worksheets("Custom
Report").Range("A1:G1"), 0)

'Insert "Manager" column between STATUS and CALLER columns
'code to format data ....
End Sub

Thanks in advance.

[Tom Ogilvy]

Actually your code would cause an error since you can't use FileToOpen as an
argument to worksheets. Also, when you open a workbook, it is the active
workbook.

You should eliminate the line

> Workbooks(fileToOpen).Activate

and the just opened workbook will be the active workbook. There is nothing
in your code that would "makes the original workbook active" that I can see.
The only other activate command is for the Worksheets("Custom Report")
which, since it is unqualified should refer to the just opened and active
workbook.
--
Regards,
Tom Ogilvy




"SP" <[email protected]> wrote in message
news:[email protected]...
> I have a workbook called TicketReport.xls which hold the code, I need
> to open a workbook that is sent to me and reformat it.
> The code starts off ok, it opens the specified workbook but then it
> makes the original workbook active. This is the code I have so far:
>
> Sub CreateReport()
> Dim fileToOpen As String
>
> 'Choose workbook to open
> fileToOpen = Application.GetOpenFilename("Text Files (*.txt),*.txt,
> Excel Files (*.xls), *.xls", 2)
>
> Workbooks.Open fileToOpen
> Workbooks(fileToOpen).Activate
> Worksheets("Custom Report").Activate
>
> 'Added MsgBox to verify which Workbook & worksheet are active
> MsgBox "The name of the active Workbook is " & ActiveWorkbook.Name
> MsgBox "The name of the active Sheet is " & ActiveSheet.Name
>
>
> 'Delete "Category" column
> myVar = WorksheetFunction.Match("Category", Worksheets("Custom
> Report").Range("A1:G1"), 0)
>
> 'Insert "Manager" column between STATUS and CALLER columns
> 'code to format data ....
> End Sub
>
> Thanks in advance.
>

[JS2004R6]

Hi SP,

It might be helpful to set references for each workbook. Then refer to each
workbook by reference. You might try adding the lines of code below to the
code you have so far.

Hope that helps.

Regards,
James

Dim wkbCustomReport As Workbook
Dim wkbOtherWorkbook As Workbook
Dim strFile As String

Set wkbCustomReport = Thisworkbook
strFile = Application.GetOpenFilename("Text Files (*.txt),*.txt, _
Excel Files (*.xls), *.xls", 2)
If strFile <> "" Then
Set wkbOtherWorkbook = Workbooks.Open strFile
End If

"SP" wrote:

> I have a workbook called TicketReport.xls which hold the code, I need
> to open a workbook that is sent to me and reformat it.
> The code starts off ok, it opens the specified workbook but then it
> makes the original workbook active. This is the code I have so far:
>
> Sub CreateReport()
> Dim fileToOpen As String
>
> 'Choose workbook to open
> fileToOpen = Application.GetOpenFilename("Text Files (*.txt),*.txt,
> Excel Files (*.xls), *.xls", 2)
>
> Workbooks.Open fileToOpen
> Workbooks(fileToOpen).Activate
> Worksheets("Custom Report").Activate
>
> 'Added MsgBox to verify which Workbook & worksheet are active
> MsgBox "The name of the active Workbook is " & ActiveWorkbook.Name
> MsgBox "The name of the active Sheet is " & ActiveSheet.Name
>
>
> 'Delete "Category" column
> myVar = WorksheetFunction.Match("Category", Worksheets("Custom
> Report").Range("A1:G1"), 0)
>
> 'Insert "Manager" column between STATUS and CALLER columns
> 'code to format data ....
> End Sub
>
> Thanks in advance.
>
>

[Tom Ogilvy]

This line will raise an error:

Set wkbOtherWorkbook = Workbooks.Open strFile

It should be

Set wkbOtherWorkbook = Workbooks.Open(strFile)

also, GetOpenFileName always returns the file name (or an array if
multiselect is set to true) or False. So this test

If strFile <> "" Then

will always be True even if the user clicks cancel and doesn't pick a file.
Since strFile is typed as String it should be

If strFile <> "False" Then



It is probably a matter of style, but if I always have the reference
ThisWorkbook defined and available (excel defines it for you, so you do), I
don't see any need to create another variable to use instead and then
initialize it with

Set wkbCustomReport = Thisworkbook

Just my opinion of course.

--
Regards,
Tom Ogilvy

"JS2004R6" <[email protected]> wrote in message
news:[email protected]...
> Hi SP,
>
> It might be helpful to set references for each workbook. Then refer to
each
> workbook by reference. You might try adding the lines of code below to the
> code you have so far.
>
> Hope that helps.
>
> Regards,
> James
>
> Dim wkbCustomReport As Workbook
> Dim wkbOtherWorkbook As Workbook
> Dim strFile As String
>
> Set wkbCustomReport = Thisworkbook
> strFile = Application.GetOpenFilename("Text Files (*.txt),*.txt, _
> Excel Files (*.xls), *.xls", 2)
> If strFile <> "" Then
> Set wkbOtherWorkbook = Workbooks.Open strFile
> End If
>
> "SP" wrote:
>
> > I have a workbook called TicketReport.xls which hold the code, I need
> > to open a workbook that is sent to me and reformat it.
> > The code starts off ok, it opens the specified workbook but then it
> > makes the original workbook active. This is the code I have so far:
> >
> > Sub CreateReport()
> > Dim fileToOpen As String
> >
> > 'Choose workbook to open
> > fileToOpen = Application.GetOpenFilename("Text Files (*.txt),*.txt,
> > Excel Files (*.xls), *.xls", 2)
> >
> > Workbooks.Open fileToOpen
> > Workbooks(fileToOpen).Activate
> > Worksheets("Custom Report").Activate
> >
> > 'Added MsgBox to verify which Workbook & worksheet are active
> > MsgBox "The name of the active Workbook is " & ActiveWorkbook.Name
> > MsgBox "The name of the active Sheet is " & ActiveSheet.Name
> >
> >
> > 'Delete "Category" column
> > myVar = WorksheetFunction.Match("Category", Worksheets("Custom
> > Report").Range("A1:G1"), 0)
> >
> > 'Insert "Manager" column between STATUS and CALLER columns
> > 'code to format data ....
> > End Sub
> >
> > Thanks in advance.
> >
> >

[SP]

Thanks for explaining the process, I've used Excel for a long time but
I'm just starting to write code.

Sal

[Gary Keramidas]

just to throw my 2 cents worth in, you probably need to add an error handler
in case there's a problem opening the file, or you just hit the cancel
button


Dim wkbCustomReport As Workbook
Dim wkbOtherWorkbook As Workbook
Dim strFile As String

Set wkbCustomReport = ThisWorkbook
strFile = Application.GetOpenFilename("Text Files (*.txt),*.txt," & _
"Excel Files (*.xls), *.xls", 2)
If strFile <> "" Then
On Error GoTo 1
Set wkbOtherWorkbook = Workbooks.Open(strFile)

End If
1:
End Sub

--


Gary


"SP" <[email protected]> wrote in message
news:[email protected]...
> Thanks for explaining the process, I've used Excel for a long time but
> I'm just starting to write code.
>
> Sal
>

[SP]

Thanks for the help, that works.

Sal