VBA Macro Save File Based On Cell Value in Current Excel Version Issue

pip74205

New Member
Joined
Feb 7, 2017
Messages
16
Code:
Dim Path As StringDim Filename As String
Path = "C:\Report\"
Filename = Range("B4")
ActiveWorkbook.SaveAs Filename:=Path & Filename & ".xlsx", FileFormat:=xlNormal

I am self teaching myself VBA and have run into an issue. Despite spending a lot of time searching, I wasn't able to get this macro to work correctly.
The above code was the only VBA code for saving a file that didn't immediately fail on me. Despite this, there are 3 issues that I can't figure out how to solve.
Thank you for any help you can provide.

First, the macro fails if I use a formula to include the date in the referenced cell, B4. (I wrapped the date formula in the Text formula so that it would appear correctly in text format).

Second, The window, "Microsoft Excel - Compatibility Checker", pops up and I do not know if it will cause any issues with the saved report. I'm unsure why it won't just save in the same version of Excel that I am using.



Third, I get a pop up saying "Defined names or formulas in this workbook may display different values when they are calculated in an earlier version of excel if they..."
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Dim Path As String
Dim Filename As String
Path = "C:\Report\"
Filename = Format(Range("B4"), "[COLOR=#ff0000]dd-mm-yyyy[/COLOR]")
ActiveWorkbook.SaveAs Filename:=Path & Filename & ".xlsx", FileFormat:=51
change the date format to suit
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,112,785
Messages
5,542,503
Members
410,559
Latest member
jordansmith6532
Top