Copy VBA or add block of vba code when using ActiveWorkbook.save to save new copy of file.

**Bnagel** · 12-20-2021, 08:50 PM

Hello ALL,

I've done tons researching online and have yet to find an example of what I'm trying to do. Below you will find a macro I'm running to export specifics of the current active workbook.
How do I add to this to copy or add vba code to the new file its creating into its "ThisWorkBook" section? The code below works perfect with saving the exact file and parts I need but is lacking the code in the new file.

Any help or direction is greatly appreciated. (Not cross posted)

''''''''export files
'''xlsx file
Dim filename2 As String
filename2 = Range("data_psu_name")
Application.EnableEvents = False
Application.DisplayAlerts = False
Sheets(Array("Project Setup Sheet", "Drop Downs")).Copy
Sheets("Drop Downs").Select
Range("A1:G50").ClearContents
Sheets("Project Setup Sheet").Select
Worksheets("Drop Downs").Visible = False
ActiveWorkbook.SaveAs filename2, FileFormat:=52, CreateBackup:=False
ActiveWorkbook.Close
Application.EnableEvents = True
Application.DisplayAlerts = True

Here is the VBA I would like to add to the new file during my export. This needs to be added to the "ThisWorkBook" section.

Private Sub Workbook_Open()
Dim my_wb As Workbook
Dim file_path As String
Dim filename8 As String
filename8 = "Library_Temp"
Application.EnableEvents = False
Application.DisplayAlerts = False
file_path = "S:\BLANK\BLANK\BLANK\BLANK\BLANK\BLANK\Library.xlsm"
Set my_wb = Workbooks.Open(FileName:=file_path, ReadOnly:=True)
ActiveWorkbook.SaveAs filename8, FileFormat:=52, CreateBackup:=False
ActiveWindow.Visible = False
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

**jolivanes** · 12-20-2021, 10:09 PM

https://stackoverflow.com/questions/...ogrammatically

As mentioned in the above article, the best way is to use a Template.

**Bnagel** · 12-20-2021, 10:29 PM

I stumbled across that post earlier today but was unable to understand and make it function.

Where do I find the "Attached"?

**jolivanes** · 12-21-2021, 01:04 AM

I deleted the attached as it is not related to your problem.
All it was is a picture on how to use code tags.

**Bnagel** · 12-22-2021, 02:06 PM

I was able to write my code to the new workbook "ThisWorkBook" area by using the following.

With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.InsertLines 1, "YOUR TEXT HERE"
.InsertLines 2, "YOUR TEXT HERE"
End With

I am not sure if there is a limit to the amount of lines but for reference I went up to .InsertLines 22.
Note: When the line you want to write to the new file has quotes ex... file_path = "C:\BLANK\BLANK\BLANK\FILE NAME HERE.xlsm"
you need to change those to double quotes such as .InsertLines 8, "file_path = ""C:\BLANK\BLANK\BLANK\FILE NAME HERE.xlsm"""

My entire code below.

Dim filename2 As String
filename2 = Range("NEW FILE NAME")
Application.EnableEvents = False
Application.DisplayAlerts = False
ThisWorkbook.VBProject.VBComponents("Module1").Export "C:\BLANK\BLANK\BLANK\code.bas"
Sheets(Array("SHEET1 NAME HERE", "SHEET2 NAME HERE")).Copy
Sheets("SHEET2 NAME HERE").Select
Range("A1:G50").ClearContents
Sheets("S").Select
Worksheets("SHEET2 NAME HERE").Visible = False
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.InsertLines 1, "Sub Workbook_Open()"
.InsertLines 2, "Dim my_wb As Workbook"
.InsertLines 3, "Dim file_path As String"
.InsertLines 4, "Dim filename7 As String"
.InsertLines 5, "filename7 = ""NEW FILE NAME"""
.InsertLines 6, "Application.EnableEvents = False"
.InsertLines 7, "Application.DisplayAlerts = False"
.InsertLines 8, "file_path = ""C:\BLANK\BLANK\BLANK\FILE NAME HERE.xlsm"""
.InsertLines 9, "Set my_wb = Workbooks.Open(FileName:=file_path, ReadOnly:=True)"
.InsertLines 10, "ActiveWorkbook.SaveAs filename7, FileFormat:=52, CreateBackup:=False"
.InsertLines 11, "ActiveWindow.Visible = False"
.InsertLines 12, "Application.EnableEvents = True"
.InsertLines 13, "Application.DisplayAlerts = True"
.InsertLines 14, "ActiveSheet.Shapes.Range(Array(""button_refresh"")).Select"
.InsertLines 15, "Selection.OnAction = ""Lib_refrest"""
.InsertLines 16, "End Sub"
.InsertLines 17, ""
End With
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.InsertLines 18, ""
.InsertLines 19, "Private Sub Workbook_BeforeClose(Cancel As Boolean)"
.InsertLines 20, "Workbooks(""FILE NAME HERE.xlsm"").Close SaveChanges:=False"
.InsertLines 21, "Kill (""FILE NAME HERE.xlsm"")"
.InsertLines 22, "End Sub"
End With
ActiveWorkbook.VBProject.VBComponents.Import "C:\BLANK\BLANK\BLANK\code.bas"
ActiveWorkbook.SaveAs filename2, FileFormat:=52, CreateBackup:=False
ActiveWorkbook.Close
Application.EnableEvents = True
Application.DisplayAlerts = True

Copy VBA or add block of vba code when using ActiveWorkbook.save to save new copy of file.

LinkBack

Thread Tools

Rate This Thread

Display

Copy VBA or add block of vba code when using ActiveWorkbook.save to save new copy of file.

Re: Copy VBA or add block of vba code when using ActiveWorkbook.save to save new copy of f

Re: Copy VBA or add block of vba code when using ActiveWorkbook.save to save new copy of f

Re: Copy VBA or add block of vba code when using ActiveWorkbook.save to save new copy of f

Re: Copy VBA or add block of vba code when using ActiveWorkbook.save to save new copy of f

Thread Information

Users Browsing this Thread

Similar Threads

VBA Code to Save Copy of a File

[SOLVED] vba code to copy data and save in other file

Save As/Save Copy As workbook to a new file name/path without closing the original

Macro to Auto save a backup copy in a seperate location OR save file with a pop up

Macro to Save As a Copy of the File, Save the Original File, & Overwrite Previous Versions

[SOLVED] VBA code to Save a Copy of a File

[SOLVED] VBA Code to Save Copy File without Macro Button and VBA Code on Copy?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions