VBA.Random characters added to Path

[Emeng]

Hi all
I have some code I wish to modify for use on a thumb drive to allow for Drive variation depending upon a particular machine’s configuration. For example on one machine it may be E:\etc\etc, on another O:\etc\etc. The code has been working fine when the drive is specified on my home machine, but not now with the Root additions, as below.
The code I have is as follows,

Sub SaveAndReMerge()   

    Dim Root As String

    Dim Path As String

    Dim FileName1 As String

    Dim FileName2 As String   

    Root = Left(ThisWorkbook.Path, InStr(1, ThisWorkbook.Path, "\"))

    Path = Root & "SAP\A4 Published Plans\"

    FileName1 = Range("E2")

    FileName2 = "A4 Published Plan"

    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs Filename:=Path & FileName1 & " " & FileName2 & ".xlsx", FileFormat:=51

        Range("D2:G2").Merge       

    Application.DisplayAlerts = True

End Sub

When running the script it trips on the ActiveWorkbook.SaveAs line, and throws a ‘1004’ error advising Excel cannot access the file ‘F:\SAP\A4 Published Plans\881B5D00’ or other set of random numbers & letters.
I’m in over my head here; can anyone explain what’s going on & how to overcome it please.
Many thanks
Mark

 

[Hui]

A few ideas

Make sure you save the file before using the Thisworkbook.path
If the workbook running the code has not yet been saved, it will return an empty string.

Try the following small changes to your code

  Root = Left(ThisWorkbook.Path, InStr(1, ThisWorkbook.Path, ":"))
  Path = Root & Application.PathSeparator & "SAP\A4 Published Plans\"

 

[Stevie]

Also, double check your folder names on your actual system don't have trailing or preceding spaces, and that they don't in the code too. I was helping someone else on this forum and getting a similar problem before I realised that this was the issue.
Good luck!

 

[Emeng]

Thanks guys.
Clearly not thinking straight!
I had the file name wrong. It begins life as an integrated plan and becomes a published plan... I had only set up a file for the integrated plan on the new thumb drive. Good pick up Steve. Many thanks to you both, regards Mark.