How do I copy a file to the same directory?

**Lee-337** · 07-07-2013, 01:43 AM

Hey everybody,

I've been using Excel for awhile now, and recently starting using vba. I think I'm getting in over my head, but I wanted to give it a shot.

What I want to do with this macro is copy a file to the same directory using the same file name, but adding the text from an input box to the end of it with an underscore. Here it is...

Sub Macro3()

 Dim strOrder As String
 Dim varGetPath As Variant
 
   strOrder = InputBox("Enter the Order number.")
   varGetPath = ThisWorkbook.FullName
   
    ThisWorkbook.SaveAs FileName:= _
        ("varGetpath" & "_strOrder"), FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        
End Sub

A couple of unexpected things are happening here. 1. The file is being saved as "varGetpath_strOrder.xlsm" and not as the file name with the inputbox text and 2. It's saving in my C:/Users/Libraries/Documents folder instead of the desktop folder it originated from. I thought it would be pretty simple to do this, but I'm sure I'm missing a good chunk of code. I'm using the new office 365.

**JBeaucaire** · 07-07-2013, 01:45 AM

Anytime you put " " around something, Excel treats it simply as text you want to use. Try removing the " " around your variables so the values IN the variables will be used instead.

Also, you're inserting a numeric value INTO the middle of the filename, so you'll need to get specific about what goes where. So maybe this, and I'm using the SaveCopyAs method so as to not affect the original file.

Sub Macro3()
Dim strOrder As String, fPATH As String, fNAME As String

    strOrder = Application.InputBox("Enter the Order number.", "Number", Type:=1)
    If strOrder = 0 Then Exit Sub
    fPATH = ThisWorkbook.Path & Application.PathSeparator
    fNAME = Replace(ThisWorkbook.Name, ".", "_" & strOrder & ".")

    ThisWorkbook.SaveCopyAs Filename:=fPATH & fNAME
        
End Sub

**Lee-337** · 07-07-2013, 01:53 AM

It worked, sort of. The file was saved in the correct directory (yes! Thank you!), but the input box text is being added to the file name after the .xlsm. Hmmm...