Please can you cand my code for an error

[ipbr21054]

Hi,
Please can you advise what ive done wrong with this line of code.
Basically the saved file is of pdf format, name is taken from cell B3 & date is taken from cell E3 but merged E3:K3


When using this line of code the file is saved as pdf & customers name all fine.

strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & ".pdf"

However when i use this line of code which pulls the dat from cell E3 i just see an error message, unable to save.

strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & .Range("E3").Value & ".pdf"

 

[Micron]

After strFileName line put
Debug.Print strFileName
Stop

Do whatever you do to make the code run and when it stops, look in the immediate window of the code editor and see what the debug line prints there. Perhaps you'll see the problem. I can't see anything wrong with your code. Are you sure that E3 is the first cell of the merge?

 

[MARK858]

Further to what @Micron has asked, what exactly is the value in E3? are you sure that there are no characters that are illegal as filenames in it?

 

[ipbr21054]

Out at present so can’t do what Micron has advised

BUT.
I think Mark has pointed me in the right direction.
The userform has a date picker which sends the date to the cell E3
I think the answer will be it’s format possibly 16/03/2023 thus unable to use the / in the saved file name.

What should I now do to change the / to a - for it to then save.

Thanks.
I’m sure this is what the issue is.

 

[MARK858]

Try...

Format(.Range("E3").Value, "dd-mm-yyyy")

 

[Micron]

If you have illegal characters, that is why I suggested you review the concatenation result before trying to use it. It's trouble shooting 101.

 

[MARK858]

If you have illegal characters, that is why I suggested you review the concatenation result before trying to use it. It's trouble shooting 101.

Which is alright if the OP knows they are looking for illegal characters and knows what characters are illegal (in this case they did)

 

[ipbr21054]

Try...

Format(.Range("B3").Value, "dd-mm-yyyy")

Do i need to put it here as if so im missing something

Private Sub Worksheet_Activate()
Range("A1").Activate
Format(.Range("E3").Value, "dd-mm-yyyy")
End Sub

 

[MARK858]

strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & Format(.Range("E3").Value, "dd-mm-yyyy") & ".pdf"

 

[MARK858]

Please note the last post has been edited