• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Rewriting VBA code when save as different file name|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Rewriting VBA code when save as different file name|VBA & Macros|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosRewriting VBA code when save as dif…
sp_PrintTopic sp_TopicIcon
Rewriting VBA code when save as different file name
Avatar
Steven Behr
Member
Members

Power Query
Level 0
Forum Posts: 85
Member Since:
January 30, 2020
sp_UserOfflineSmall Offline
1
July 1, 2020 - 10:53 am
sp_Permalink sp_Print sp_EditHistory

Hi,

 

I have VBA codes in my database indicating the workbook name such as Spare_Parts_Order_Matic_2.2.xlsm.

If I need to change/SaveAs the file to Site_Spare, is there any examples where from the SaveAs point, Change the VBA coding automatically from Spare Parts to Site Spare. The code below as an example on what I got on various procedures.

Sub ClearOrderLine()
'
' ClearOrderLine Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
    Sheets("ProductToLocationStockQTY").Select
    Range( _
        "A2,E2,I2,M2,Q2,U2,Y2,AC2,AG2,AK2,AO2,AS2,AW2,BA2,BE2,BI2,BM2,BQ2,BU2,BY2,CC2,CG2" _
        ).Select
    Selection.ClearContents
    Sheet9.Range("A1").Select
    Application.Run "'Spare_Parts_Order_Matic_2.2.xlsm'!PtoLArow1"
    Application.Run "'Spare_Parts_Order_Matic_2.2.xlsm'!PtoLBrow1"
 

Reason being, I have to change 126 lines in VBA in different procedures with Site Spare but realized why the macro in the file wont work because I saved it as Site spare with Spare Parts Code.

 

Thanks

Steve

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
2
July 2, 2020 - 2:33 am
sp_Permalink sp_Print
Hello,

it's not easy, because I didn't fully understand the question... but I will leave here an example

first: in my example I have one folder with only two excel workbooks

In one Workbook with the name: "OpenMyMacro.xlsm", 
I have one module with one macro with the name "HelloWorld" with a msgbox "Hello from: " & Application.ThisWorkbook.Name

In the second Workbook put these codes in Userform module:

 

Private Sub CommandButton1_Click()

Dim getFileName As String
Dim xCount As Integer
Dim cell As Range
Dim cellRange As Range
Dim f As String
Dim WorkbookName As String
Dim MacroName As String
Dim FileToClose As String
Dim FileToOpen As String
Dim x As String

xCount = CountFilesInFolder(Application.ThisWorkbook.Path)

Debug.Print xCount

If xCount <> 2 Then Exit Sub ' in my example if files count is not 2 (function)

Call Get_All_Files_In_Folders

ActiveSheet.Select

Set cellRange = Range("B1:B2")

For Each cell In cellRange
     If cell.Value <> Application.ThisWorkbook.Name Then
          f = cell.Value
     End If
Next cell

Debug.Print f

'WorkbookName = "OpenMyMacro.xlsm"
WorkbookName = f ' workbook name
MacroName = "HelloWorld" ' macro name

FileToClose = Application.ThisWorkbook.Path & "\" & WorkbookName
FileToOpen = Application.ThisWorkbook.Path & "\" & WorkbookName

'determine if file exists
On Error Resume Next
x = CreateObject("scripting.filesystemobject").FileExists(FileToClose)
On Error GoTo 0

Debug.Print x

If x = False Then GoTo FileNoExists:

'open another workbook and run macro
Application.EnableEvents = False
Application.ScreenUpdating = False

Application.Run "'" & FileToOpen & "'!" & MacroName

'close another workbook
Workbooks(Dir(FileToClose)).Close SaveChanges:=False

Application.EnableEvents = True
Application.ScreenUpdating = True

Exit Sub

FileNoExists:
     MsgBox "file does not exist or could not be found", vbCritical, "Error"
End Sub

Public Function CountFilesInFolder(fileDir As String) As Integer ' count files in folder

Dim xFile As Variant

Dim x As Integer

If Right(fileDir, 1) <> "\" Then fileDir = fileDir & "\"
xFile = Dir(fileDir)

While (xFile <> "")
     x = x + 1
     xFile = Dir
Wend

CountFilesInFolder = x

End Function

 

Public Sub Get_All_Files_In_Folders() ' get all files path & names in folder

Dim MyPath As String, MyFolderName As String, MyFileName As String
Dim i As Integer
Dim AllFolders As Object, AllFiles As Object

MyPath = Application.ThisWorkbook.Path & "\"

'List all folders
Set AllFolders = CreateObject("Scripting.Dictionary")
Set AllFiles = CreateObject("Scripting.Dictionary")
AllFolders.Add (MyPath), ""
i = 0

Do While i < AllFolders.Count
     Key = AllFolders.Keys
     MyFolderName = Dir(Key(i), vbDirectory)
     Do While MyFolderName <> ""
          If MyFolderName <> "." And MyFolderName <> ".." Then
               If (GetAttr(Key(i) & MyFolderName) And vbDirectory) = vbDirectory Then
                    AllFolders.Add (Key(i) & MyFolderName & "\"), ""
               End If
          End If
          MyFolderName = Dir
     Loop
     i = i + 1
Loop

'List all files
For Each Key In AllFolders.Keys
     MyFileName = Dir(Key & "*.*")
     Do While MyFileName <> ""
          AllFiles.Add (MyFileName), Key
          MyFileName = Dir
     Loop
Next

ActiveSheet.Select
ActiveSheet.Cells.Delete

ActiveSheet.[A1].Resize(AllFiles.Count, 1) = WorksheetFunction.Transpose(AllFiles.Items) ' file path
ActiveSheet.[B1].Resize(AllFiles.Count, 1) = WorksheetFunction.Transpose(AllFiles.Keys) ' file name

If Not AllFolders Is Nothing Then Set AllFolders = Nothing
If Not AllFiles Is Nothing Then Set AllFiles = Nothing

End Sub

 

I can change the name of the Workbook "OpenMyMacro.xlsm " whenever I want, and I will always be able to open the macro

 

Regards,

 

Miguel

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
3
July 3, 2020 - 7:29 pm
sp_Permalink sp_Print

Hello,

Even without feedback on what you really want, I think you were wondering how to replace code in Vbproject

For that, you don't need to write any Macro, and having a Macro to run this procedure, it would require  the VBproject is unlocked

and VBProject has a tool to do that

I'm using Office 2013 Pro (English version on one laptop, and Portuguese on another)


Just open the VBA window, choosing Edit & Find on the menu bar, clicking the Find button on the Standard toolbar, or by pressing "Ctrl+F" or "Ctrl+H", to open a dialog box.

The Portuguese version is "Ctrl + L"

With this tool, you can search and replace everything you want at once on VBProject.


Regards,

Miguel

Capturar.JPGImage Enlarger
sp_PlupAttachments Attachments
  • sp_PlupImage Capturar.JPG (46 KB)
Avatar
Steven Behr
Member
Members

Power Query
Level 0
Forum Posts: 85
Member Since:
January 30, 2020
sp_UserOfflineSmall Offline
4
July 4, 2020 - 4:57 pm
sp_Permalink sp_Print

Yes sorry Migel, I'm coming back to work tomorrow sydney time as I am on holidays for 4 days. Your explanation is something that I am interested in reading and testing the VBA language. I will let you know in coming days...without my work computer I'm totally blind! But really appreciate your help Steve

Avatar
Steven Behr
Member
Members

Power Query
Level 0
Forum Posts: 85
Member Since:
January 30, 2020
sp_UserOfflineSmall Offline
5
August 1, 2020 - 8:24 am
sp_Permalink sp_Print

Hi Miguel,

You are right its not easy but I do have vaious errors simply because of the structure of my database and very long modules and codes. - It had taken 

However, with that in mind. There is a different database which is very small but handy, I used your Macro code and found that it was useful. In fact, that is exactly what I needed.

I think that because the amount of modules in my other database with various codes it searched throughout the DB that it came up with various errors to much to mentioned.

But I do thank you for this its made my other databases worthwhile redoing

 

Thanks

Steve

Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
6
August 3, 2020 - 2:29 am
sp_Permalink sp_Print
You're welcome
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Kumud Patel
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Gilbert Lemek
Ashleigh Farquharson
Jayz Luu
Fred Smith
Charles DeGraffenreaid
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Forum Stats:
Groups: 3
Forums: 24
Topics: 6360
Posts: 27812

 

Member Stats:
Guest Posters: 49
Members: 32340
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.