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

 

[MrChuckles69]

I asked as already on this thread there are three diff ways ... and (to me) its ambiguous where the code slots in and what comes out ...

 

[MrChuckles69]

thanks Mark!

 

[Dan Waterloo]

I'll bow out for now. It becomes difficult when three people are trying to offer solutions.

 

[MrChuckles69]

Run time error 13 Type Mismatch ... no bananas

 

[MARK858]

thanks Mark!

Happy it helped and welcome to the forum

 

[MrChuckles69]

Dan ... thank you ... that tweak made it work

Great help all, thank you.

 

[bobsan42]

I mean like this:

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

Actually you can use any format. if it is only numbers that you need you can do it like this:

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(range("H6").Value,"yyyy-mm-dd")
ActiveWorkbook.SaveAs FileName:=Path & FileName1 & Filename2 & ".xlsx", FileFormat:=xlOpenXMLWorkbook
End Sub