Moving files using VBA

Hi,

I am trying to automate moving files and I cannot get it to work. I was able to create the new folders automatically but not moving the invoices.

I have an Excel file where I have sheet called serviceinvoice
In A column I have user number and in B column I have invoice number

In cell D1 I have a file path.

In that folder I have pdf invoices and folders per user

The name of the pdf invoice starts with the invoice number (12 characters)
The name of the user folder starts with the user number (4 characters)

Process that I am looking for is:
1. check the invoice number from excel sheet
2. find the pdf file that starts with that number
3. go and get a user number from column A
4. find the subfolder where the name starts with this number
5. move the invoice to this folder
6. if folder cannot be found, skip to next invoice
7. do this to all invoices listed in the Excel sheet.

This is how far I have got, but it doesn't work and I don't understand why. (And ChatGPT is clueless)

I have checked that the invoices have the same numbers and that the subfolders are there. Please help!

Sub MoveInvoices()

Dim ws As Worksheet
Dim folderPath As String
Dim invoiceNumber As String
Dim userNumber As String
Dim invoiceFileName As String
Dim userFolder As String

' Set worksheet
Set ws = ThisWorkbook.Sheets("serviceinvoice")

' Get folder path from cell D1
folderPath = ws.Range("D1").Value

' Loop through rows
For i = 3 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
invoiceNumber = Left(ws.Cells(i, 2).Value, 12) ' Use only the first 12 characters
userNumber = Left(ws.Cells(i, 1).Value, 4) ' Use only the first four characters

' Construct file paths
invoiceFileName = invoiceNumber & "*" ' Assumes invoice files are PDFs
userFolder = userNumber ' The user folder starts with the user number

' Check if invoice file exists
If Dir(folderPath & "" & invoiceFileName) <> "" Then

' Check if user folder exists, if not, skip this invoice
If Dir(folderPath & "" & userFolder, vbDirectory) <> "" Then

' Move invoice to user subfolder
FileCopy folderPath & "" & invoiceFileName, folderPath & "" & userFolder & "" & invoiceFileName
Kill folderPath & "" & invoiceFileName ' Remove original file

End If
End If

Next i

End Sub

My code works on my test file, so I'm not sure what else to suggest.

Please Login or Register  to view this content.

You need to include the backslash as per my code;

Please Login or Register  to view this content.

I can't get it to work. I am using google drive desktop folders but I did test on my local folders and it wasn't working there either. I have separate macro to create the user folders and that is working, so the file path must be ok. My invoice pdf names are format XX-2023-0152 firstname surname and my user subfolders are named 1234 firstname surname. And I have tried to copy the file name from the Excel sheet to the file name so that the dashes are definitely the same.