How To Copy Module(s) To Workbook In Different Path
Jun 21, 2013
Want to Copy a Module(s) to another workbook in a different path and also password protect it at the same time using VBA.
I need to copy some Module(s) from One WorkBook to another using VBA..
I have used the following code to good effect, however my TargetWb is not at the same path, so I need some modification in this code if that's possible and also this VBA Module which is Copied and Pasted in the Target WOrkbook needs to be Password Protected so that it cannot be tampered.
The code which I'm using currently is:
Code:
Sub CopyModule(SourceWB As Workbook, strModuleName As String, _
TargetWB As Workbook)
' copies a module from one workbook to another
' example:
' CopyModule Workbooks("Book1.xls"), "Module1", _
[Code]...
Code:
Sub Try()
Call CopyModule(Activeworkbook, "Module1", TargetWb)
on this to pass the TargetWb as a variable which can be generated in the code used for generating the File using a Macro..
End Sub
In the above code The SourceWB would be the ActiveWorkbook and the Target Workbook is the file created using another Macro from several sheets of the Active Workbook and its created in a folder with a specific name and therefore the Path is one more level inside than the Active Workbook.
if the Transferred Module can be Passworrd Protected and Locked for editing..for the end user.
View 9 Replies
ADVERTISEMENT
Jul 14, 2006
Is there anyway to copy a module from one workbook to another using a macro?
View 6 Replies
View Related
Mar 26, 2009
I have been trying for some days to copy a module to an external workbook with VBA. I have about 40 workbooks that have the same format. although I developed these sheets. I am not the person who uses them. I have to on occasion alter the code for some reason, its getting tiresome.
I don't think I can use the personal workbook method for this as it is not me using the workbooks. I don't even know some of the people that use them. This courses me problems because of the number of workbooks. I want to be able to maintain the code by having to write it once and propagate it through the necessary files via the VBA.
I have code from C Pearson that copies a module to another workbook.
I have posted this here just in case. Most threads on this subject just direct you here any way....
View 9 Replies
View Related
Aug 26, 2009
If possible, Can a module be copied into a new workbook from the master workbook. When the one I have does not copy the modules when creating a new workbook. If so it would make my life a bit easier.
View 9 Replies
View Related
Aug 27, 2007
I'm trying to save a copy an excel file in the same directory of the original file. The code is pretty standard but it does not work if the file is stored in C: (only c: ) Is that a bug or am I doing something wrong? Anyways here is the code in case someone is interested in trying it out:
Sub CreateCopy()
ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls", _
InitialFileName:="CMS_" & Format(Now(), "mm-dd-yyyy"))
If fileSaveName <> False Then
MsgBox "Backup copy saved as: " & fileSaveName
End If
End Sub
View 5 Replies
View Related
Aug 3, 2014
I have an Excel sheet that contains a few thousand folder paths in the first column. The first few cells in the column look similar to below.
C:UsersNameDesktopFolder Copy12010360
C:UsersNameDesktopFolder Copy12010361
C:UsersNameDesktopFolder Copy12010362
C:UsersNameDesktopFolder Copy12010363
C:UsersNameDesktopFolder Copy12010364
C:UsersNameDesktopFolder Copy12010365
I am trying to create a macro that will copy any folders that exist in any of the paths listed in the first column. The folders should be copied to the path of the open workbook containing the macro. Below is the macro I have currently, much of which was taken from information I found in this thread [URL]....
VB:
Sub wrapper3()
x = 1
Set fs = CreateObject("Scripting.FileSystemObject")
While Sheets("Air").Cells(x, 1) <> ""
v = InStrRev(Sheets("Air").Cells(x, 1), "")
dest = ActiveWorkbook.Path & Mid(Sheets("Air").Cells(x, 1), v, 99)
[Code]...
This code seems to work fine if all of the folders exist to be copied. My problem is that some do not and it is creating a "Path Not Found" error for which I need a fix. If the folder doesn't exist at the path nothing should be copied and the next path can be evaluated.
View 2 Replies
View Related
May 19, 2009
I am successfully opening a .csv file using a variable value stored in a named range in my Main file (the variable includes the directory and path).
I copy data from the .csv file to the Main file then I need to close the .csv file without saving but I want to do that by using the
Windows("xxxx").Activate
command where "xxxx" is the namedrange in my Main file which stores the .csv filename (without the directory and path prefix).
I can use the
ActiveWindow.ActivatePrevious
command but if I have another workbook open, this one closes instead of the .csv file I opened from the macro.
I realise this is probably very basic and I've searched the forums but can't find any identical postings.
View 5 Replies
View Related
Feb 1, 2010
1) I need to add an edit check
2) have a copy of Active Workbook Path stamped onto spreadsheet with date and time to create a visual record of where the file has been saved (described after the code below).
1) I need to verify that two cells (S7 and S9) are not blank before running my code below (=IF(OR(S7<>"",S9<>""),RUN CODE,"You must select your Provider or Division before you can save this document")).
- If both of these cells are blank a message box should notify the user that they must select the provider and/or division before they can continue with the save.
- If one or more of these cells are not blank the code below should run.
View 3 Replies
View Related
Jan 24, 2013
Let's say i have 2 Modules on my VBA forms, is it possible to Copy all the Codes in Module 1 and Paste it to Module 2 by using a Command Button?
View 1 Replies
View Related
Mar 20, 2007
I will shortly be running a project over a twenty four hour period, where a group of people will be inputting data into a workbook (one worksheet per hour), and what I would like to know is if it is possible, to input some coding into a thisWorkbook module to action a save on the workbook every time an entry is placed in column A (of each worksheet) which is divisable by 10 (a10, a20, a30 etc).
View 2 Replies
View Related
Jan 18, 2014
I am creating a File/Workbook using MACRO, however i need to ensure that one of the Modules in the created workbook remains protected and hidden...
Since there are lot of these files which are created, I would be interested in protecting them via a macro..
View 4 Replies
View Related
Mar 15, 2007
I have multi- sheet workbook that opens to a specific ws and to a specific cell.
Application.Goto Reference:=Worksheets("Menu").Range("A1"), _
One of the several ws is a form; when the user saves it, all other ws are deleted and the wb is saved as a new file name. (Need to retain other macros for future functionality, seems easiest to just delete the unneeded sheets).
Is their a way to modify the sheet reference from "menu" to "sheet-name" (will be a constant). Because it is likely that additional sheets will be added in the future to this new wb, I'd like for it to open to a specific ws.
As my VBA skills are "in development" (basement level, just now), please provide a complete answer (assuming this can be done).
View 9 Replies
View Related
Mar 13, 2012
I have a workbook which I regularly upload that cannot have macros within it; however, to update and process the data, I must utilize VBA Code.
I tried to work around this by storing my macros in a module in another workbook. My process is to have the VBA Code Module from the other workbook open and have the active workbook be the one I want to change, then I run the VBA Code from the other workbook.
This used to work. However, I am recently getting problems with the Code not running correctly. Sometimes it only runs the code if the affected cells are not hidden; other times it will not run the code at all!
View 4 Replies
View Related
May 6, 2007
I have a workbook (Program_1.xls) which references another workbook (Program_2.xls). I want to close Program_1.xls using code in Program_2.xls.
When the Program_1.xls is closed, the code stops.
Program_1.xls code
Sub Program_1()
MsgBox "This is program #1"
Application.Run "Program2.xls!Program_2"
End Sub
Program_2.xls code
Sub Program_2()
Source = "C:Documents and SettingsCohenMy Documentsprogram3.xls"
target = "C:Documents and SettingsCohenMy Documentsprogram1.xls"
Workbooks("Program1.xls").Close savechanges:=False
' ---> code halts here
MsgBox "This is program #2"
FileCopy Source, target
End Sub
How do I get the final 2 lines of code to execute?
View 9 Replies
View Related
May 15, 2008
I want to write some code that runs everytime the workbook is initialized. I found out that the code should be placed in the following "Private Sub":
Private Sub Workbook_Open()
'My code
End Sub
I know that this code should be placed in the Private Module of the Workbook! I just can not find where is the Private Module of the Workbook in Excel 2007... =/
View 3 Replies
View Related
Aug 4, 2006
I have a very basic cut copy and paste macro in a workbook (ThisWorkbook) that fails when it reaches the line :
Workbooks.Open Filename:=ThisWorkbook.Path & "" & DataWbk, UpdateLinks:=3
When i hover over this line it states the actual (and correct) path of the workbooks that the macro is relevant to, but the error says the path is unrecognisable.
However, in other similar macros that i have that work ok, when i hover over this line it states : <Object variable or With block variable not set>.
Is there any way that i can set the 'ThisWorkbook.Path' to read this? To my knowledge i have not recorded these two macros differently, yet they are producing these contrasting results.
View 7 Replies
View Related
Mar 21, 2013
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
View 5 Replies
View Related
Jul 9, 2013
How to delete another excel private module of the Workbook Object (ThisWorkbook) VBA codes of the workshook event.
This code to run from my PERSONAL.XLSB
Sub Macro1()
Windows("Book1.xls").Activate
ActiveWorkbook.Vbproject
Don't know what other codes I need to put after this
End Sub
This code to be deleted via Macro1 above code
Private Sub Workbook_Open()
Call CreateMenu
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteMenu
End Sub
View 2 Replies
View Related
May 30, 2007
I've used the following code to export macros from one workbook to another but I was wondering how you export macros from the ' Sheet' code module to the 'Sheet' code module of another workbook.
Sub Import_Macro()
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
strRet = .Lines(1, .CountOfLines)
End With
Set wkb = Workbooks("Test Workbook.xls")
wkb.VBProject.VBComponents. Import ("G:SCSSCSALLReportsVB MacrosGeneral.bas")
wkb.VBProject.VBComponents.Import ("G:SCSSCSALLReportsVB MacrosMJ Selections.bas")
With wkb.VBProject.VBComponents("ThisWorkbook").CodeModule
.DeleteLines 1, .CountOfLines '//Delete Codes already wrriten
.InsertLines 1, strRet '// Write Code
End With
End Sub
View 8 Replies
View Related
Oct 31, 2008
how can i open a different path of the workbook in the listbox. if my listbox there only show the file name.
View 2 Replies
View Related
Jul 23, 2014
here I have a few good and perfect codes, the issue is my systems changed so I need it to look in some different places for data.
basically I need to create a file path that includes the data from a cell. this cell is found be searching a table full of data.
see the code below
Code:
Sub saveandprint()
Dim MyPath As String
MyPath = MyPathstart & "Invoice" & "" & Format(Date, "dd-mm-yy")
MyPath1 = "Z:Invoices Temp GKL " & Range("B16").Value & Format(Now, "hhmm")
[Code]....
View 4 Replies
View Related
Jan 10, 2008
Is there a way to display the location of an active workbook on my local drive and save it into a varible. I'm new to Excel and VBA but I'm trying to create a template to use for multiple vehicles. When the macro is run...
1) the workbook will have to identify what Workbook and worksheet it is in and save it as a varible so that it can access it later. Each workbook is specific to a vehicle.
*(The reason is because I will need to refer to the address of the original workbook when pasting the copied data from the "OilChart" Workbook.)
2) Copy the vehicle identification # and save as a varible. (Dim vehicleNumber As Variant)
3) Open another workbook on the local drive (OilChart.xls)
4) Run the macro in the new workbook (OilChart.xls) to update the data
5) Search Column B in (OilChart.xls) for matches of the vehicleNumber, and for all rows that match, select and copy all rows and paste it back into the orig workbook (Vehicle Specific Workbook).
This is the code I have so far.
Private Sub btnFind_Click()
Dim vehicleNumber As Variant
Dim sourceLocation As String
Dim sourceFileName As String
Dim fileName As String
Application. ScreenUpdating = False
I have'nt figured out how to do step 5 yet so if anybody has any ideas I'd be open to them. I tried looking at Aaron Bloods Kickbutt VBA Find Function but I got a little lost on how his code would relate to my code. It also looks like it doesn't copy or paste anything to another workbook.
View 8 Replies
View Related
Feb 16, 2008
I work on multiple workbooks. They all calculate a lot of functions and I am forced to create multiple workbooks to do these calculations. Linking to other workbooks basically displays the results of workbook1 and works from there.
But...
I need to keep an overview of these files, so I store them in different folders all within the same 'master' folder. So let's say: master folder is FolderMaster, and this contains folderA, folderB, folderC. FolderA contains workbookA1, workbookA2; folderB contains workbookB1, workbookC2 and finally folderC contains workbookC1 and workbookC2.
Suppose that this hierarchy might change. Moving eg workbook A2 to folderB. This would cause a #REF if it is linked in another workbook.
So I thought: organize your hierarchy in a 'master workbook' and store all paths to the individual files in a worksheet. If I link to a specific folder I do not directly link to the actual folder, but would use INDIRECT and grab the filepath from the masterworkbook. If the path to a workbook change I do not have to update all individual links, but only change the path in the masterworkbook. Right?
But 'how' is the big one here. How do you formulate your path to a file? It can not be the entire file path since that's variable... (sometimes 'hd:User1:....'; sometimes 'HD:User2'..."). So I think I need something as mentioned in this office help thing. But I have no clue. I do not want to use VBA, I hoped for another solution to this.
View 4 Replies
View Related
Feb 13, 2013
I am using Excel 2010 and late binding to generate reports. The sheets are blank and unused. I have the UserForm being opened on Workbook_Open and the actual VBA for doing the work in a separate macro in the workbook. All the posts I have found are dealing with accessing cells from the UserForm and I haven't found any dealing with connecting a macro to the UserForm output.
My question is this: I want to return from the UserForm the flags of which reports to run to the (unopened) macro containing the code to do so. How do I open the macro and pass the variables to it?
The UserForm is creatively called "SelectionForm" and the macro is called "DailyFTP" with a Main sub as the entry. I know I can use the code under to capture the radio button and checkbox choices. I have other variables like the names of files set as global variables in the DailyFTP macro. The macro doesn't exist until SelectionForm opens it, so they must be set after the macro is in memory.
View 6 Replies
View Related
Mar 15, 2008
I am trying to create a new sheet from an existing sheet. I only need to copy the sheets and not the code from the original sheet. But, when I copy the first sheet the code module also gets copied to this new sheet.
View 5 Replies
View Related
Sep 30, 2009
I tend to stumble when trying to pull certain characters from strings, not sure how to stop and start my search.
View 4 Replies
View Related
Oct 26, 2011
The folder which contains the Excel file I'm working on is
C:Usersar3DocumentsKey Performance Indicators
however if I place =INFO("directory") in a cell I get
C:Usersar3Documents
Is there a function where I can get the full path of the workbook in the cell?
View 2 Replies
View Related
Aug 18, 2006
If you have a workbook or a template open you can find out the full path of the file from the .FullPath property. My problem is that when a user double-clicks my .xlt file (to create a new workbook based on it), I want the code in the Workbook_Open event to be able to tell the full path of the template it was created from (because the code will later go on to save the new workbook under a date related filename in the same directory that the template was located in). I haven't been able to find a property that gives me any clue as to the location of the template this new workbook is based on.
View 5 Replies
View Related
Sep 15, 2006
I am having trouble getting a macro to open a specific file from an open workbook in the same folder based on the current path of the open file. The application is this... I have two files in the same folder. The first is for data entry and it contains a macro button that will open the second which is a template to which the data in the first will be copied. I can easily do this with a fixed path to the second file, however, I would like to be able to move or copy the folder containing these files to other locations or computers without having to change the path each time. I would like to base the path to the second file minus the file name on the first (already open file) path. Assuming that the first file containing the desired macro button is already open, here is one of many of my attempts...
Workbooks.Open " ActiveWorkbook.PathJob Tracker -.xls"
Assuming the second file is the active workbook, I achieved a similar path assignment when saving the file...
ActiveWorkbook.SaveAs "Application.Path/Jobs Active/" & "Job Tracker -" & Range(" '[Job Tracker -.xls]Specifications'!$A$6").Value
View 2 Replies
View Related
Dec 28, 2006
I've go a shared Workbook, which will be distributed among several users and stored in different places. The workbook uses the following
Sub savemeas()
Worksheets("data").Visible = True
Sheets("data").Copy
Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:="d:" & Range("a1").Value, _
FileFormat:=xlText, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=True
End Sub
to save the sheet "data" as a text file with a name based on the value of cell a1. All I need is to modify the code so that the target path would not be
ActiveWorkbook.SaveAs Filename:="d:" & Range("a1").Value
but be the same as the source Workbook's - so that I wouldn't have to modify the code for each user separatly, because the sheet would alwayas be saved in the same folder as the current path of the source workbook.
View 4 Replies
View Related