CODEWITHSUNDEEP
vbaexcel
xtina1231
xtina1231

Reputation: 125

How to do 'Save As' using VBA

I would like to ask for helping my code below. I got this when I recorded a macro upon saving a new file to my desktop.

I am having issues with the Change Directory line. This changes will happen every time a different user is using this macro which can also depend on the value of e.

Also, part of the filename varies which depends on the value of d.

Please help me improve my codes. Thanks!

Dim b, c, d, e As String

b = MsgBox("This file has to be saved. Click OK.", vbOKCancel, "Save to SharePoint")

ActiveWorkbook.Save

c = MsgBox("Save this file to local", vbOKOnly, "Save to Local")

d = InputBox("This shift record falls on what week?", "Save Shift Record", "YYYYMMDD-YYYYMMDD")

e = InputBox("Enter your EID", "Save Shift Record", "EID")

'this line give error ~~~~~ 
ChDir "C:\Users\ma.cristina.i.gumato\Desktop"

ActiveWorkbook.SaveAs Filename:= "C:\Users\d\Desktop\MANILA_ShiftRecord_" + d + ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Upvotes: 0

Views: 615

Answers (1)

DiegoAndresJAY
DiegoAndresJAY

Reputation: 706

Does this work for you?

Dim b, c, d, e As String

b = MsgBox("This file has to be saved. Click OK.", vbOKCancel, "Save to SharePoint")

ActiveWorkbook.Save

c = MsgBox("Save this file to local", vbOKOnly, "Save to Local")

d = InputBox("This shift record falls on what week?", "Save Shift Record", "YYYYMMDD-YYYYMMDD")

e = InputBox("Enter your EID", "Save Shift Record", "EID")

ActiveWorkbook.SaveAs Filename:= "C:\Users\" & environ("username") & "\Desktop\MANILA_ShiftRecord_" + d + ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Upvotes: 1

Related Questions