Changing VBA output destination

[LifesAGarden]

I have the code below. which appends data to a table in Access. It also renames the file(adds "completed" to the front) and puts it in another folder. The issue is, it puts the renamed files in my "Documents" folder, when i want them to be placed in the following folder, "C:\Users\user1\Desktop\TEst\Completed\".

How or where do i change the VBA so that it saves the file to "C:\Users\user1\Desktop\TEst\Completed\"

Sub Import_Multi_Excel_Files()


Dim InputFile As String
Dim InputPath As String


InputPath = "C:\Users\user1\Desktop\TEst\Work\"
InputFile = Dir(InputPath & "*.xls")


Do While InputFile <> ""
If InputFile Like "Completed*" Then
Else
DoCmd.TransferSpreadsheet acImport, , "Checks", InputPath & InputFile, True '< The true is for column headers
Name InputPath & InputFile As "Completed" & InputFile
End If
InputFile = Dir
Loop


End Sub

 

[LifesAGarden]

Also, would it be possible to include the file name as a column in the database table? Not even sure where to begin to inject that into the code above.

 

[LifesAGarden]

I was able to solve this with the following code.

Some changes that I added were in "Name InputPath & InputFile As InputPath & "Completed" & InputFile". By adding the bold faced text, it now saved the file in teh same directory in which it pulls the file from.

Another change I added was the code below. This places the file name in a column so that i can keep track of which file the data comes from.

strsql = "UPDATE Checks SET Checks.Filename = '" & InputFile & Format(Now, "mmddyyyy_hhmmss") & "' " _
& "WHERE (((Checks.Filename) Is Null));"
'execute the action queryt to update the File Name in all imported records
CurrentDb.Execute strsql


Sub Import_Multi_Excel_Files()


Dim InputFile As String
Dim InputPath As String




InputPath = "C:\Users\user1\Desktop\TEst\Work\"
InputFile = Dir(InputPath & "*.xls")


Do While InputFile <> ""
If InputFile Like "Completed*" Then
Else
DoCmd.TransferSpreadsheet acImport, , "Checks", InputPath & InputFile, True '< The true is for column headers
strsql = "UPDATE Checks SET Checks.Filename = '" & InputFile & Format(Now, "mmddyyyy_hhmmss") & "' " _
& "WHERE (((Checks.Filename) Is Null));"
'execute the action queryt to update the File Name in all imported records
CurrentDb.Execute strsql
Name InputPath & InputFile As InputPath & "Completed-" & Format(Now, "mmddyyyy_hhmmss") & " " & InputFile
End If
InputFile = Dir
Loop


End Sub