Changing the active workbook
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" wrote in message
...
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.
|