Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A bit long - but please help
Hello,
I am really having a problem with the following. I can get most of this to work but I'm having problems assigning the macros to the buttons. I have the file I'm trying to update as a variable. I know that I have gotten this to work before, but I can't now. For some reason the macro will look to the workbook where I have my procedure rather than to the file that I'm trying to update. You can see that I am trying both - and either the one with the variable won't work or the one without will reference the workbook with the procedure. As always, any help would be much appreciated. Regards, Anita Sub CopyProc() Dim SourceModule As VBIDE.CodeModule Dim DestModule As VBIDE.CodeModule Dim wB As String, wBCopy As String Dim sourcePath As String Dim destPath As String Dim frow Dim NewFileColumn Dim OldFileColumn Dim SourceDir Dim DestDir Dim TableRow Dim sourceData Dim Budget Dim PayrollFile Dim VarianceFile Dim SourceDirectory Dim DestinationDirectory Dim fileWextention sourceData = "CostCenters" Budget = 1 PayrollFile = 2 VarianceFile = 3 SourceDirectory = 4 DestinationDirectory = 5 TableRow = 2 'tableRow is the row of the first file on the FileList sheet Workbooks("AddingModule2.xls").Activate wB = Sheets(sourceData).Cells(TableRow, VarianceFile) Do Application.ScreenUpdating = False 'wBCopy = Sheets(sourceData).Cells(TableRow, VarianceFile) fileWextention = wB & ".xls" Application.StatusBar = "Processing " & fileWextention sourcePath = Sheets(sourceData).Cells(TableRow, SourceDirectory) destPath = Sheets(sourceData).Cells(TableRow, DestinationDirectory) Application.ScreenUpdating = False Application.DisplayAlerts = False Workbooks.Open Filename:=fileWextention, updatelinks:=False Workbooks(fileWextention).Activate ActiveWorkbook.Unprotect ("nope") 'Try This Dim VBComp As VBComponent Set VBComp = _ Workbooks(fileWextention).VBProject.VBComponents.A dd(vbext_ct_StdModule) VBComp.Name = "CarAllowance" Application.Visible = True 'End Try Set SourceModule = _ Workbooks("AddingModule2.xls").VBProject.VBCompone nts("Module3").CodeModule Set DestModule = _ Workbooks(fileWextention).VBProject.VBComponents(" CarAllowance").CodeModule Range("w11").Select ActiveSheet.Buttons.Add(1123, 231.6, 129.75, 14.25).Select Selection.Characters.Text = "Benefits" With Selection.Characters(Start:=1, Length:=13).Font .Name = "MS Sans Serif" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.OnAction = fileWextention & "!Benefits" Range("v11").Select ActiveSheet.Buttons.Add(1272, 231.7, 129.75, 14.25).Select Selection.Characters.Text = "Print Benefits" With Selection.Characters(Start:=1, Length:=19).Font .Name = "MS Sans Serif" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.OnAction = "Benefitsprint" Range("w11").Select 'adding sheet Sheets("Bank_Charges").Copy Befo=Sheets(3) Sheets("Bank_Charges (2)").Name = "Benefits" ActiveSheet.Unprotect password:="nope" Range("b10").Value = "Notepad for Benefits" Range("h10").Value = "80800" ActiveSheet.Shapes("Button 2").Select Selection.OnAction = "Home_Benefits" Cells.Select Selection.Replace What:="Bank_Charges", Replacement:="Benefits", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False ActiveSheet.Protect password:="nope" 'changing cells to linked rather than hard coded Sheets("xxxx").Select Sheets("xxxx").Unprotect password:="nope" 'benefit cells Range("N24:O24").Copy Destination:=Range("n19") Range("N20").Select Selection.Copy Range("N19:O19").Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Range("N19:O19").Select Selection.Replace What:="Insurance", Replacement:="Benefits", LookAt _ :=xlPart, SearchOrder:=xlByRows, MatchCase:=False ActiveSheet.Protect password:="nope" ActiveWorkbook.Protect password:="nope" Workbooks(fileWextention).Save Workbooks(fileWextention).Close TableRow = TableRow + 1 Workbooks("AddingModule2.xls").Activate wB = Sheets("CostCenters").Cells(TableRow, VarianceFile) Application.StatusBar = "" Loop Until wB = "" Application.StatusBar = "" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File Path Too Long? Not Anymore! Check out Long Path Tool | Excel Discussion (Misc queries) | |||
http://CannotDeleteFile.net - Cannot Delete File? Try Long Path ToolFilename is too long? Computer Complaining Your Filename Is Too Long? TheLong Path Tool Can Help While most people can go about their businessblissfully unaware of the Windo | Excel Discussion (Misc queries) | |||
Long Long Long Nested If Function | Excel Discussion (Misc queries) | |||
Interrupting long calculations in Excel? Hi all, I am having a verylong calculation in Excel using VBA. Of course I know I can hit ESC orCTRL+BREAK to interrupt the VBA program. But I found due to too long runningprocess, the Excel window stop to | Excel Discussion (Misc queries) | |||
Clearing cells takes long, long time | Excel Discussion (Misc queries) |