It's a date! ... or could be with help

[MrChuckles69]

A friend asked for help with VBA ... getting a file to save (invoices) based on clicking an image and saving the document based on two cell values

The first one is the customer name and the second is a date

I took on the challenge and googled and ... sometimes it worked, sometimes not, worked on mine, didnt on his etc ...

The date cell was a problem as it didnt work unless it was a text cell, so we ran with that for now

I'd like to finish the job but despite looking on google, Mr Excel and others ... most answers arent quite the same ... looking at 'TODAY' as a data and not teh cell

I'm stuck!

This is the code that saves a file at the moment ... might not be pretty or perfect and so if theres an easier way ... all ears!

D2 is the cell with the client name and H6 has a date in it


Thank you guys!

Sub Picture1_Click()
Dim Path As String
Dim FileName1 As String
Dim Filename2 As String
Path = "/Users/me/Documents/mine/"
FileName1 = Range("D2")
Filename2 = Range("H6")
ActiveWorkbook.SaveAs FileName:=Path & FileName1 & Filename2 & ".xlsx", FileFormat:=xlOpenXMLWorkbook
End Sub

 

[MARK858]

Maybe if you want the cell to be an actual date (change formatting to suit)...

Filename2 = Format(CDate(Range("H6")), "dd_mm_yyyy")

 

[Dan Waterloo]

Hi Mr Chuckles. Welcome to the Board. If FileName2 is actually text then I think that the code you have given should work. Can you describe what your problem is?

 

[bobsan42]

Keep Range H6 as DATE value. Then format the value as text using one of the 2 below:

Filename2 =Format(range("H6"),"yyyy-mm-dd")

or

Filename2 =Application.WorksheetFunction.Text(range("H6").Value,"[$-en-GB]dd mmmm yyyy")

 

[MrChuckles69]

Dan ... I muddled my way through and used (as it happened) something from years ago on youtube.

I used what was there and it worked if H6 was text only ... as in was a date and changed the format to text to get it to work

I'd like that cell to be a date format again and for the VBA to same it as say .... Waterloo-03Apr20

I dont know what to change

 

[Dan Waterloo]

Yeah, Mark858 and Bobsan42, I would have recommended something similar, but I'd like to hear what Mr. Chuckles thinks the problem is. How can his own solution work for him but not for his friend? Likely because one of them has H6 in a numeric format and one has it in a text format. I can see this going around in several circles before getting resolved.

 

[MrChuckles69]

Bob ...
You mean .... like this?


Sub Picture1_Click()
Dim Path As String
Dim FileName1 As String
Dim Filename2 =Application.WorksheetFunction.Text(range("H6").Value,"[$-en-GB]dd mmmm yyyy")
Path = "/Users/me/Documents/mine/"
FileName1 = Range("D2")
Filename2 = Range("H6")
ActiveWorkbook.SaveAs FileName:=Path & FileName1 & Filename2 & ".xlsx", FileFormat:=xlOpenXMLWorkbook
End Sub

 

[MrChuckles69]

Dan ...

It works because its text ... when it was a date format it tried to save to a directory /04/2020 ... hence why changed the cell format to text

Some posts suggested this can be overcome by not taking the / as a directory ... hence seeking help how to do this

No circles, no to you to me ... just a straight question

 

[MARK858]

Sub Picture1_Click()
    Dim Path As String
    Dim FileName1 As String
    Dim Filename2 As String
    Path = "/Users/me/Documents/mine/"
    FileName1 = Range("D2")
    Filename2 = Format(CDate(Range("H6")), "ddmmmyy")
    ActiveWorkbook.SaveAs Filename:=Path & FileName1 & & Filename2 & ".xlsx", FileFormat:=xlOpenXMLWorkbook
End Sub

Edit: Left the path the same as you say it saves currently but seems strange to me that there is no drive letter but then haven't played with a Mac for a while.

 

[Dan Waterloo]

If H6 is actually in a date format then try Mark's solution with the format below:

Filename2 =Format(range("H6"),"ddmmmyy")