-4

Example cell A1.value= "StreetName & OtherStreetName" (as text)

Save file as A1.value

The cell may or may not have the "&" in the cell. Most of the time it does not and my code works fine, but when it is in the cell it reads the "&" as part of the VBA code and not as a text item in a string like I need it to.

Dim UName As String
UName = Environ("username")

ActiveWorkbook.SaveAs FileName:="C:\Users\" & UName & "\Desktop\" & range.("A1").value & ".xlsm", _
   FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

cell A1 = "StreetName & OtherStreetName"

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
NOUSE94
  • 1
  • 2
  • 2
    I don't understand how an ampersand would ever be interpreted from the cell value as part of the vba coding. I can't reproduce the issue. – dwirony Aug 31 '23 at 21:55
  • I don't think you've shown us enough code for anyone to be able to help you. – Tim Williams Aug 31 '23 at 23:11
  • its in the vba because i want the cell value to be in the vba as a string to reference where to save it . – NOUSE94 Sep 01 '23 at 00:05
  • What exactly happens when there's a `&` in the A1 value? What does it do *instead* of work fine? – Tim Williams Sep 01 '23 at 22:12
  • I can reproduce the issue. Are you using OneDrive to sync your desktop? If the regular desktop is being redirected (eg to `C:\Users\[userName]\OneDrive - Contoso\Desktop` then `CreateObject("Wscript.Shell").SpecialFolders("Desktop")` should give you the actual location. Try saving the file there instead of `C:\Users\[userName]\Desktop` – Tim Williams Sep 01 '23 at 22:33

0 Answers0