0

I am importing daily data and manipulating it. I want to save the file as the date of the data which is picked up from A4.

The data initially comes in to me as a CSV file and the data is of the format 27/06/2020. I've reformatted the data during the manipulation, but regardless of this, when I come to save the file I get a

Runtime error 1004 Microsoft Excel cannot access the file "C:\Users\steve\Desktop\27\06\D2D75130"

I'm unclear about whether or not (and how) to change this to text format so that it saves - but I like the idea of the date staying in date format to help with my data analysis. Or maybe I should add a text string in front of the date, i.e., "Sales(then the date)" - if that would work? Here is my existing code:

' This is to save the file on the desktop using the date (content of cell A4) as the filename
' It also saves the file with the name 'Latest Report' in the Reports Folder


Sub FileNameAsCellContent()

    Dim FileName As String
    Dim Path As String
    Application.DisplayAlerts = False
    Path = "C:\Users\steve\Desktop\" 'change this if you want to save it somewhere else
    FileName = Sheets("Summary").range("A4").Value & ".xlsm"

    ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbookMacroEnabled

    ' Change the format here which matches with the extention above. Choose from the following link
    ' http://msdn.microsoft.com/en-us/library/office/ff198017.aspx

    Application.DisplayAlerts = True


    ' This saves it in the reports folder as 'Latest Report'

    ChDir "C:\Users\steve\Desktop\Reports"
    ActiveWorkbook.SaveAs FileName:= _
    "C:\Users\steve\Desktop\Reports\Latest Report.xlsm", FileFormat:= _
    xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Stephen Callaghan
  • 57
  • 5

1 Answers1

0

If Sheets("Summary").range("A4").Value has a / character in it, you need to replace or get rid of that first, because that character cannot be used in a filename. Use the following line instead to remove that character.

FileName = Replace(Sheets("Summary").range("A4").Value, "/", "") & ".xlsm"

You could also replace it with a new valid character:

FileName = Replace(Sheets("Summary").range("A4").Value, "/", "-") & ".xlsm"

Replace function VBA Documentation

Here's a list of all the illegal characters:

enter image description here

Remove illegal characters while saving workbook Excel VBA

braX
  • 11,506
  • 5
  • 20
  • 33