| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro launched from another file only partially works
Hi-
I am stumped I have a file (File 1) with a marcro that activates File2 and turns control over to File 2 macro. File2 macro does copying and pasting from File1 to File2, then causes File1 to close and File2 to saveas File1. Problem occurs when the File2 macros causes File1 to close. File2 macro stops and never gets saved as File1 name. The uncanny thing is that when I just run the File2 macro from File2, it works fine. But if it gets called from FIle1 is stops as soon as File1 is closed. Note: File1 --we don't know the name of....user launches from File1 and code defines the name and path. We do know the name of File2 (fxRM_Update.xls) Code for FIle 1: Sub RunUpdate() 'Update button in user file Account Summary Sheet runs this macro Unprotect Dim updfile As String Dim usrfile As Workbook Dim backname As String Dim p As String p = ActiveWorkbook.Path updfile = p & "\" & "fxRM_Update.xls" Set usrfile = ActiveWorkbook backname = p & "\" & "Backup" & usrfile.Name currentuserfile = p & "\" & usrfile.Name 'Save backup copy of user's file, in same folder If InStr(1, p, "\xlstart", vbTextCompare) = 0 Then 'If XLSTART is not in the path usrfile.SaveCopyAs filename:=backname 'do the save Else 'Else if XLSTART is in the path 'Do nothing, i.e. don't save End If 'Insert user filename in "filename" cell Sheets("Lookup").Select Range("filename") = ActiveWorkbook.Name 'Insert user file path in "path1" cell Range("path1") = ActiveWorkbook.Path 'Find & open fxRM_Update.xls If Dir(updfile) = "" Then 'If fxRM_Update not there, error message MsgBox ("fxRM_Update.xls not found. Move fxRM_Update.xls to this folder.") Exit Sub End If 'On Error Resume Next If IsFileOpen(updfile) Then 'If fxRM_Update.xls is open 'Do nothing, No error Else 'If not open, Workbooks.Open filename:=updfile 'Open it End If 'Activate & Unprotect fxRM_Update.xls Windows("fxRM_Update.xls").Activate Unprotect 'Copy user file name to fxRM_Update.xls usrfile.Activate Sheets("Lookup").Select Application.GoTo Reference:="Filename" Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Lookup").Select Application.GoTo Reference:="UserFilename" ActiveSheet.Paste 'Copy user file path to fxRM_Update.xls usrfile.Activate Sheets("Lookup").Select Application.GoTo Reference:="path1" Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Lookup").Select Application.GoTo Reference:="path2" ActiveSheet.Paste 'Copy user file version to Update file, Old Version cell usrfile.Activate Sheets("Lookup").Select Application.GoTo Reference:="CurrentVersion" Application.CutCopyMode = False Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Lookup").Select Application.GoTo Reference:="OldVersion" ActiveSheet.Paste Range("K7").Select 'Run Update macro Application.Run ("fxRM_Update.xls!Update") End Sub Code from File2: Sub Update() '1/2008 - this adds data from the user file to fxRM_Update_v1.32.2.xls 'Requires Update filename: "fxRM_Update.xls" 'Runs from Update file 'Copies data from bk1 (User file) to "fxRM_Update.xls" (Update file) '"fxRM_Update.xls" becomes new user file, saved as user's filename 'Update2 defines user file in fxRM_Update.xls & activates it 'Copy AccountSummary data from User file to Update file Update2 'every time you run Update2, you are reactivating Userfile Sheets("AccountSummary").Select Range("C6:C8").Select Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("AccountSummary").Select Range("C6").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Update2 Sheets("AccountSummary").Select Range("K7:M506").Select Application.CutCopyMode = False Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("AccountSummary").Select Range("K7").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Update2 Sheets("AccountSummary").Select Application.CutCopyMode = False Range("A1").Select 'Copy TradeHistory data from User file to Update file Sheets("TradeHistory").Select Range("A6:Av15000").Select Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("TradeHistory").Select Range("A6").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Update2 Sheets("Analysis").Select Range("A10").Select Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Analysis").Select Range("A10").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Update2 Sheets("Analysis").Select Range("C10").Select Application.CutCopyMode = False Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Analysis").Select Range("C10").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select 'Copy AnalysisSheet data from User file to Update file Update2 Sheets("Analysis").Select Range("A17:F56").Select Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Analysis").Select Range("A17").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Update2 Sheets("Analysis").Select Range("K17:M56").Select Application.CutCopyMode = False Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Analysis").Select Range("K17").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Update2 Sheets("Analysis").Select Range("O17:S56").Select Application.CutCopyMode = False Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Analysis").Select Range("O17").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Update2 Sheets("Analysis").Select Range("A16:AV16").Select Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Analysis").Select Range("A16").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Update2 ActiveWorkbook.Close (savechanges = True) 'MACRO Stops here when you start with RUNUPDATE from user file. 'runs fine when you run UPDATE from fxRM_Update file Windows("fxRM_Update.xls").Activate 'Protect 'Save Update file as User file name ' Dim bk As Workbook Dim saveString As String Dim sstr2 As String Dim path2 As String path2 = ActiveWorkbook.Path sstr2 = Worksheets("lookup").Range("d42").Value saveString = path2 & "\" & sstr2 Application.DisplayAlerts = False ActiveWorkbook.SaveAs filename:=saveString Protect 'ActiveWorkbook.savechanges = False ActiveWorkbook.Close End Sub Sub Update2() 'Defines user file in fxRM_Update.xls 'Update macro calls this macro each time it must return to user file Dim bk As Workbook, bk1 As Workbook Dim sstr As String Set bk = Workbooks("fxRM_Update.xls") sstr = bk.Worksheets("lookup").Range("userfilename").Valu e Set bk1 = Workbooks(sstr) bk1.Activate End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro launched from another file only partially works
How do you start your macro?
Do you use a shortcut key? Does that shortcut key include the shiftkey? Does the problem go away if you drop the shift from the shortcut key combination? If you have a workbook that has a workbook_open event or an auto_open procedure, then if you open that file with the shift key held down, you stop those procedures from running. The shift-key in the shortcut combination confuses excel/vba to stop after you open a file using one of those shortcut keys. Andyjim wrote: Hi- I am stumped I have a file (File 1) with a marcro that activates File2 and turns control over to File 2 macro. File2 macro does copying and pasting from File1 to File2, then causes File1 to close and File2 to saveas File1. Problem occurs when the File2 macros causes File1 to close. File2 macro stops and never gets saved as File1 name. The uncanny thing is that when I just run the File2 macro from File2, it works fine. But if it gets called from FIle1 is stops as soon as File1 is closed. Note: File1 --we don't know the name of....user launches from File1 and code defines the name and path. We do know the name of File2 (fxRM_Update.xls) Code for FIle 1: Sub RunUpdate() 'Update button in user file Account Summary Sheet runs this macro Unprotect Dim updfile As String Dim usrfile As Workbook Dim backname As String Dim p As String p = ActiveWorkbook.Path updfile = p & "\" & "fxRM_Update.xls" Set usrfile = ActiveWorkbook backname = p & "\" & "Backup" & usrfile.Name currentuserfile = p & "\" & usrfile.Name 'Save backup copy of user's file, in same folder If InStr(1, p, "\xlstart", vbTextCompare) = 0 Then 'If XLSTART is not in the path usrfile.SaveCopyAs filename:=backname 'do the save Else 'Else if XLSTART is in the path 'Do nothing, i.e. don't save End If 'Insert user filename in "filename" cell Sheets("Lookup").Select Range("filename") = ActiveWorkbook.Name 'Insert user file path in "path1" cell Range("path1") = ActiveWorkbook.Path 'Find & open fxRM_Update.xls If Dir(updfile) = "" Then 'If fxRM_Update not there, error message MsgBox ("fxRM_Update.xls not found. Move fxRM_Update.xls to this folder.") Exit Sub End If 'On Error Resume Next If IsFileOpen(updfile) Then 'If fxRM_Update.xls is open 'Do nothing, No error Else 'If not open, Workbooks.Open filename:=updfile 'Open it End If 'Activate & Unprotect fxRM_Update.xls Windows("fxRM_Update.xls").Activate Unprotect 'Copy user file name to fxRM_Update.xls usrfile.Activate Sheets("Lookup").Select Application.GoTo Reference:="Filename" Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Lookup").Select Application.GoTo Reference:="UserFilename" ActiveSheet.Paste 'Copy user file path to fxRM_Update.xls usrfile.Activate Sheets("Lookup").Select Application.GoTo Reference:="path1" Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Lookup").Select Application.GoTo Reference:="path2" ActiveSheet.Paste 'Copy user file version to Update file, Old Version cell usrfile.Activate Sheets("Lookup").Select Application.GoTo Reference:="CurrentVersion" Application.CutCopyMode = False Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Lookup").Select Application.GoTo Reference:="OldVersion" ActiveSheet.Paste Range("K7").Select 'Run Update macro Application.Run ("fxRM_Update.xls!Update") End Sub Code from File2: Sub Update() '1/2008 - this adds data from the user file to fxRM_Update_v1.32.2.xls 'Requires Update filename: "fxRM_Update.xls" 'Runs from Update file 'Copies data from bk1 (User file) to "fxRM_Update.xls" (Update file) '"fxRM_Update.xls" becomes new user file, saved as user's filename 'Update2 defines user file in fxRM_Update.xls & activates it 'Copy AccountSummary data from User file to Update file Update2 'every time you run Update2, you are reactivating Userfile Sheets("AccountSummary").Select Range("C6:C8").Select Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("AccountSummary").Select Range("C6").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Update2 Sheets("AccountSummary").Select Range("K7:M506").Select Application.CutCopyMode = False Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("AccountSummary").Select Range("K7").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Update2 Sheets("AccountSummary").Select Application.CutCopyMode = False Range("A1").Select 'Copy TradeHistory data from User file to Update file Sheets("TradeHistory").Select Range("A6:Av15000").Select Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("TradeHistory").Select Range("A6").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Update2 Sheets("Analysis").Select Range("A10").Select Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Analysis").Select Range("A10").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Update2 Sheets("Analysis").Select Range("C10").Select Application.CutCopyMode = False Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Analysis").Select Range("C10").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select 'Copy AnalysisSheet data from User file to Update file Update2 Sheets("Analysis").Select Range("A17:F56").Select Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Analysis").Select Range("A17").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Update2 Sheets("Analysis").Select Range("K17:M56").Select Application.CutCopyMode = False Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Analysis").Select Range("K17").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Update2 Sheets("Analysis").Select Range("O17:S56").Select Application.CutCopyMode = False Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Analysis").Select Range("O17").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Update2 Sheets("Analysis").Select Range("A16:AV16").Select Selection.Copy Windows("fxRM_Update.xls").Activate Sheets("Analysis").Select Range("A16").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Update2 ActiveWorkbook.Close (savechanges = True) 'MACRO Stops here when you start with RUNUPDATE from user file. 'runs fine when you run UPDATE from fxRM_Update file Windows("fxRM_Update.xls").Activate 'Protect 'Save Update file as User file name ' Dim bk As Workbook Dim saveString As String Dim sstr2 As String Dim path2 As String path2 = ActiveWorkbook.Path sstr2 = Worksheets("lookup").Range("d42").Value saveString = path2 & "\" & sstr2 Application.DisplayAlerts = False ActiveWorkbook.SaveAs filename:=saveString Protect 'ActiveWorkbook.savechanges = False ActiveWorkbook.Close End Sub Sub Update2() 'Defines user file in fxRM_Update.xls 'Update macro calls this macro each time it must return to user file Dim bk As Workbook, bk1 As Workbook Dim sstr As String Set bk = Workbooks("fxRM_Update.xls") sstr = bk.Worksheets("lookup").Range("userfilename").Valu e Set bk1 = Workbooks(sstr) bk1.Activate End Sub -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average formula only works partially..... | Excel Worksheet Functions | |||
| Formula Partially Works, Please Help | Excel Worksheet Functions | |||
| go to the same path of the excel file being launched | Excel Programming | |||
| Excel minimizes when a linked file launched | Excel Discussion (Misc queries) | |||
| XLSSTART\ does not works when Excel is launched from other application | Excel Discussion (Misc queries) | |||
Linear Mode
