Please check this code

[BMWE65]

Hi all,

I hope someone can help!

I have created an invoice in Excel, along with a Macro 'save' button.
When I click the Macro save button, it saves the invoice using the data in cells B11, F11 and F13 as the file name, as well as today's date.
This is the code that I have used:

Public Sub SaveAsC6()
ThisFile = Range("B11").Value
ThisFile2 = Range("F11").Value
ThisFile3 = Range("F13").Value
ActiveWorkbook.SaveAs Filename:=ThisFile & "-" & ThisFile2 & "-" & ThisFile3 & "-" & Format(Date, "yyyymmdd")
If Err = 0 Then MsgBox "Save successful", vbInformation
End Sub
Sub Button20_Click()
Application.Dialogs(xlDialogPrint).Show
End Sub

That all works fine and no problems.


What I have now decided to do is to remove the 'today date' part of the code. This is because when I want to print the invoice at a future date, it will only ever show me today's date, rather than the date the invoice was created.

So in cell F9, I inserted an object, (Calender Control 11.0), and found this code that I have inserted into the sheet:

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "dd/mm/yyyy"
ActiveCell.Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("F9"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub


I then amended the 'save' macro code for it to also include the data in cell F9, which is the date I select from the calender control, so this is what I amended the 'save' macro code too:

Public Sub SaveAsC6()
ThisFile = Range("B11").Value
ThisFile2 = Range("F11").Value
ThisFile3 = Range("F13").Value
ThisFile3 = Range("F9").Value
ActiveWorkbook.SaveAs Filename:=ThisFile & "-" & ThisFile2 & "-" & ThisFile3 & "-" & ThisFile4
If Err = 0 Then MsgBox "Save successful", vbInformation
End Sub
Sub Button20_Click()
Application.Dialogs(xlDialogPrint).Show
End Sub


The problem is that when I run the macro and click the button, I get the following messge:

Run Time Error 1004
MS Excel cannot access the file (pathname). There are several reasons:
*The file or path does not exist
*The file is being used by another program
*The workbook you are trying to save has the same name as a ..(cuts off here).

When I click debug, this line is highlighted:

ActiveWorkbook.SaveAs Filename:=ThisFile & "-" & ThisFile2 & "-" & ThisFile3 & "-" & ThisFile4

Any ideas?

Many thanks

 

[VoG]

Try

ThisFile3 = Format(Range("F9").Value,"yyyymmdd")

 

[BMWE65]

Perfect - thank you sooo much!