.xl01 file type

[Kuledoode]

I have a program that opens an existing workbook, adds a date to the name and saves it as a new file. The code says to save it as a .xlsm file type but it ends up as a .xl01 or a .xl04 or a .xl010 file type. Excel does not recognize this file type when attempting to open in explorer. I cannot find any information on this file type. Please assist!

Sub Check_File()

Dim File As String, DirFile As String


File = "Event and Conference Points Table " & Format(ActiveCell, "yyyy-mm-dd" & ".xlsm")
DirFile = "C:\Users\Jeff\LH Points\"


If Dir(DirFile & File) = "" Then
    Workbooks.Open FileName:=DirFile & "Event and Conference Points Table Template.xlsm"
    ActiveWorkbook.SaveAs FileName:=DirFile & File
Else
    Workbooks.Open FileName:=DirFile & File
End If
End Sub


Private Sub CommandButton1_Click()  'changes dates back 28 days
    Range("A3").Value = Range("G1")
End Sub


Private Sub CommandButton2_Click()  'changes dates forward 28 days
    Range("A3").Value = Range("H1")
End Sub


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Call Check_File
End Sub

 

[JoeMo]

This Works
Thanks 1 000 000 for helping out a beginner

You are welcome - glad we could help.

 

[Kuledoode]

Now the only thing I have to get is the date of the newly created file to show up in cell A2

File = "Event and Conference Points Table " & Format(ActiveCell, "yyyy-mm-dd") & ".xlsm"

 

[JoeMo]

Now the only thing I have to get is the date of the newly created file to show up in cell A2

File = "Event and Conference Points Table " & Format(ActiveCell, "yyyy-mm-dd") & ".xlsm"

A2 of the newly created file in the active sheet of that file?

 

[Kuledoode]

Yes, I create a new workbook called Event and Conference Points Table 2019-05-18.xlsmfor that day based on a selection of a date in another workbook called Calendar. I would like to have that date appear in cell A2 of the new workbook.

 

[JoeMo]

Yes, I create a new workbook called Event and Conference Points Table 2019-05-18.xlsmfor that day based on a selection of a date in another workbook called Calendar. I would like to have that date appear in cell A2 of the new workbook.

This will produce the current date (date file was created). If that's different from the date in the workbook called Calendar you will need to modify the date below:

If Dir(DirFile & File) = "" Then
    Workbooks.Open Filename:=DirFile & "Event and Conference Points Table Template.xlsm"
    ActiveWorkbook.SaveAs Filename:=DirFile & File, FileFormat:=52
    With ActiveWorkbook.ActiveSheet.Range("A2")
        .Value = Date
        .NumberFormat = "yyyy-mm-dd"
    End With
    'rest of code

 

[Kuledoode]

I need the date of the active cell in Calendar, be it a date of today or a day in the past/future, regardless of when the file was created.

 

[JoeMo]

I need the date of the active cell in Calendar, be it a date of today or a day in the past/future, regardless of when the file was created.

Then you need to tell us more about Calendar. What's the name of the workbook? Is it Calendar.xlsm or .....? Is Calendar open when you are creating the new workbook? What worksheet & cell in Calendar holds the date you want to use?

 

[Kuledoode]

It is Calendar.xlsm
Calendar is always open when the new file is created. New file generation is prompted directly from Calendar.
Sheet 1, date selected (active.cell).
All dates have a hyperlink to allow user to open file on direct click of date. See bottom code.

[/COLOR]Sub Check_File()

Dim File As String, DirFile As String


File = "Event and Conference Points Table " & Format(ActiveCell, "yyyy-mm-dd") & ".xlsm"
DirFile = "C:\Users\Jeff\LH Points\"


If Dir(DirFile & File) = "" Then
    Workbooks.Open FileName:=DirFile & "Event and Conference Points Table Template.xlsm"
    ActiveWorkbook.SaveAs FileName:=DirFile & File, FileFormat:=52
Else
    Workbooks.Open FileName:=DirFile & File
End If
End Sub


Private Sub CommandButton1_Click()  'changes dates back 28 days
    Range("A3").Value = Range("G1")
End Sub


Private Sub CommandButton2_Click()  'changes dates forward 28 days
    Range("A3").Value = Range("H1")
End Sub


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Call Check_File
End Sub


[COLOR=#333333]

 

[JoeMo]

Using activecell from a workbook to get the date is living dangerously. In any case here it is. Change the file extension in bold red font to match the extension on Calendar.

If Dir(DirFile & File) = "" Then
    Workbooks("Calendar.[B][COLOR=#ff0000]xlsm[/COLOR][/B]").Activate  'Change file extension to suit
    calDate = ActiveCell.Value
    Workbooks.Open Filename:=DirFile & "Event and Conference Points Table Template.xlsm"
    ActiveWorkbook.SaveAs Filename:=DirFile & File, FileFormat:=52
    With ActiveWorkbook.ActiveSheet.Range("A2")
        .Value = calDate
        .NumberFormat = "yyyy-mm-dd"
    End With
    'rest of code