Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Activate Worksheet
I did get a response to my question yesterday from Jim Thomlinson (thanks!),
but it looks like he refers to a specific file name in his code. For example: Set wkbOpend = Workbooks.Open("C:\Test.xls"). I really need to use the variable FName to refer to a file since the file name will change all the time. Is there any way to do this? Thanks as always. The following code gives me a list of filenames I've chosen from the open file dialog (file 1). Other code then opens the first file in the list (call it file 2), and if a specific sheet name exists in that file, it runs a macro from the file the list of names is in (file 1). The macro runs in the correct file (file 2), but I run into problems when I need to run a second macro from file 1 because I need to activate file 2 again and make sure the macro runs there. I've been able to do this so far by activating Window 2, but if the user has multiple files open I'm dead. Since I assign FName to a variable in the code, can't I refer to FName to reactivate that file? I've tried a few things but none have worked so far; I'm sure it's something simple. Sub ShowFileNames() Dim FName As Variant Dim xNames As Range Sheets("Files").Select Range("A1").Select Set xNames = Range("xFiles") FName = ActiveCell For Each FName In xNames Workbooks.Open FName If SheetExists("PT - Selected Mfr") Then ClearPivottable BuildPivottable Else End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Activate Worksheet
Workbooks(FName.Value).Activate
It would be better if you didn't activate the workbooks though. Dim rBNames as Range Dim rBName as Range Dim wbActive as Workbook dim wsActive as Worksheet Set rBNames = ThisWorkbook.Sheets("Files").Range("xFiles") For Each rBName in rBNames.Cells Set wbActive = Workbooks.Open(rBName.Value) If SheetExists("PT - Selected Mfr", wbActive) Then Set wsActive = wbActive.Sheets("PT - Selected Mfr") ClearPivotTable wsActive BuildPivotTable wsActive End If Next rBName Function SheetExists(sName as String, wb as Workbook) as Boolean 'replace all references to ActiveWorbook with wb End Function Sub ClearPivotTable(ws as Worksheet) 'replace all references to ActiveSheet with ws End Sub Sub BuildPivotTable (ws as Worksheet) 'replace all references to ActiveSheet with ws End Sub -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com cottage6 wrote: I did get a response to my question yesterday from Jim Thomlinson (thanks!), but it looks like he refers to a specific file name in his code. For example: Set wkbOpend = Workbooks.Open("C:\Test.xls"). I really need to use the variable FName to refer to a file since the file name will change all the time. Is there any way to do this? Thanks as always. The following code gives me a list of filenames I've chosen from the open file dialog (file 1). Other code then opens the first file in the list (call it file 2), and if a specific sheet name exists in that file, it runs a macro from the file the list of names is in (file 1). The macro runs in the correct file (file 2), but I run into problems when I need to run a second macro from file 1 because I need to activate file 2 again and make sure the macro runs there. I've been able to do this so far by activating Window 2, but if the user has multiple files open I'm dead. Since I assign FName to a variable in the code, can't I refer to FName to reactivate that file? I've tried a few things but none have worked so far; I'm sure it's something simple. Sub ShowFileNames() Dim FName As Variant Dim xNames As Range Sheets("Files").Select Range("A1").Select Set xNames = Range("xFiles") FName = ActiveCell For Each FName In xNames Workbooks.Open FName If SheetExists("PT - Selected Mfr") Then ClearPivottable BuildPivottable Else End If |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repost: Activate Worksheet
If you are going to open multiple workbooks - what is the significance of
file2. I can certainly show you how to use a public variable to store a reference to a file, but running another macro on "file2" doesn't make much sense if "file2" is actually several files. The code you show guarantees the user will have multiple workbooks open if the range Files contains multiple workbook names. The following code gives me a list of filenames I've chosen from the open file dialog (file 1). The code you posted may work on a copy of that list, but the posted code doesn't do that. (not that it makes any difference to your question - but in case you thought it does, maybe I don't understand the question). Regards, Tom Ogilvy "cottage6" wrote in message ... I did get a response to my question yesterday from Jim Thomlinson (thanks!), but it looks like he refers to a specific file name in his code. For example: Set wkbOpend = Workbooks.Open("C:\Test.xls"). I really need to use the variable FName to refer to a file since the file name will change all the time. Is there any way to do this? Thanks as always. The following code gives me a list of filenames I've chosen from the open file dialog (file 1). Other code then opens the first file in the list (call it file 2), and if a specific sheet name exists in that file, it runs a macro from the file the list of names is in (file 1). The macro runs in the correct file (file 2), but I run into problems when I need to run a second macro from file 1 because I need to activate file 2 again and make sure the macro runs there. I've been able to do this so far by activating Window 2, but if the user has multiple files open I'm dead. Since I assign FName to a variable in the code, can't I refer to FName to reactivate that file? I've tried a few things but none have worked so far; I'm sure it's something simple. Sub ShowFileNames() Dim FName As Variant Dim xNames As Range Sheets("Files").Select Range("A1").Select Set xNames = Range("xFiles") FName = ActiveCell For Each FName In xNames Workbooks.Open FName If SheetExists("PT - Selected Mfr") Then ClearPivottable BuildPivottable Else End If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
repost: How do I activate an option button? | Excel Discussion (Misc queries) | |||
Worksheet Consolidation - Repost requested | Excel Discussion (Misc queries) | |||
Worksheet formatting stumper!! (repost) | Excel Discussion (Misc queries) | |||
Repost: How do you identify a worksheet as last? | Excel Programming | |||
Repost: Worksheet Change Method (Bob Philips, Ron De Bruin) | Excel Programming |