Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the active workbook
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the active workbook
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" wrote in message oups.com... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the active workbook
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the active workbook
Thanks for the help, that works.
Sal |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the active workbook
Thanks for explaining the process, I've used Excel for a long time but
I'm just starting to write code. Sal |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing the active workbook
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" wrote in message oups.com... Thanks for explaining the process, I've used Excel for a long time but I'm just starting to write code. Sal |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing an active cell format in VBA | Excel Discussion (Misc queries) | |||
Closing Hidden Workbook when Active Workbook is Closed | Excel Programming | |||
changing active cell | Excel Programming | |||
copy worksheet from closed workbook to active workbook using vba | Excel Worksheet Functions | |||
Preventing opening workbook inside active workbook. | Excel Programming |