VBA for referring to workbook in directory by partial name based on a value in cell and copy and paste

[shahdelsol]

I am trying to come up with VBA that finds a workbook in directory based on a value in A14 that is part of book name. Let's say A14 = 123456, there is a workbook named 123456 abc.xlsx in directory ( I have named this book ws1) and then copy a few values from that book and paste it in the current book ( I have named it ws2). The way it works, if A14 of ws2 has a value then macro will look for that value as a partial name in directory once found will simply copy and past a few cells into ws2 and does the same thing for A15 and through A32 if not found message box will say file doesn't exist. This is what I have come up with but I know it has some issue and I am asking for help on correction. Also if it matters in directory there are hundreds of files that they all have the same name format 123456 abc.xlsx , 123459 ada.xlsx and so on. Thanks

 Dim j As Integer
Dim ws1 As Workbook
Dim ws2 As Workbook
For j = 14 To 32
FileNum = Cells(j, 1)
ws1 = "C:\Order Entry\Orders\" & FileNum & " *" & ".xlsx"
ws2 = Workbooks("Invoice.xlsm")
If ws2.Sheet1.Cells(j, 1) <> "" Then
 ws2.Sheet1.Cells(j, 2) = ws1.Sheet1.Range("f1")
 ws2.Sheet1.Cells(j, 6) = ws1.Sheets("sheet1").Range("B17")
 ws2.Sheet1.Cells(j, 7) = ws1.Sheets("sheet1").Range("D25")
 
 Next j
 
 Else
 
 MsgBox "Your file doesn't exist"
 
 End If
 
 End Sub

 

[shahdelsol]

Thanks for the new code. I think this is close to what I am looking for. Did you try this on your own? Did it work on your end? Obviously I changed Dir to my location but what is interesting that I get an error that says: " Run time error 1004 , sorry we couldn't find 123456 abs.xlsx. Is it possible that it was moved, renamed or deleted" If it doesn't know what the file name is how come it knows what is after the 6 digit number (Abs)? Basically it knows what exactly the file name is but it still says it doesn't exist. I am wondering if you get the same error on your end?

 

[Truiz]

I tested the code and it was working, I was getting that error at first but I noticed made a mistake and fixed it I had an extra space that was causing the name of the file to not match... make sure you are writing the directory exactly as it should be including the final ""

 

[shahdelsol]

I tested the code and it was working, I was getting that error at first but I noticed made a mistake and fixed it I had an extra space that was causing the name of the file to not match... make sure you are writing the directory exactly as it should be including the final ""

I am still getting error. This is how my Dir looks like. Did I do it correctly?

 sFile =Dir ("C:\Users\XRX\Desktop\test\"  &  FileNum & "*" & ".xlsx")

 

[Truiz]

Copy paste this

sFile = Dir("C:\Users\XRX\Desktop\test\" & FileNum & "*" & ".xlsx")

 

[shahdelsol]

Copy paste this

sFile = Dir("C:\Users\XRX\Desktop\test\" & FileNum & "*" & ".xlsx")

Still the same error I am not sure what I am doing wrong!

 

[shahdelsol]

Still the same error I am not sure what I am doing wrong!

And just to clarify this is where I get the error. Maybe this line has issue:

Set ws1 = Workbooks.Open(sFile, , True)

 

[shahdelsol]

Still the same error I am not sure what I am doing wrong!

Another issue that I found is even if I change number in (j, 1) let's say A14, I still get the same message for the same file name. For example if type 123456 in A14, it says "file name 123456 abc.xlsx cannot be found...." now if I go to A14 and I type in another number like 789456, it also says , " file name 123456 abc.xlsx cannot be found...." It should have said 789456 cbd.xlsx cannot be found, shouldn't it?

 

[Truiz]

Another issue that I found is even if I change number in (j, 1) let's say A14, I still get the same message for the same file name. For example if type 123456 in A14, it says "file name 123456 abc.xlsx cannot be found...." now if I go to A14 and I type in another number like 789456, it also says , " file name 123456 abc.xlsx cannot be found...." It should have said 789456 cbd.xlsx cannot be found, shouldn't it?

very odd... do the file names start with whats on cell A14? is A14 contain any special characters extra spaces that need to be taken care of...???

 

[shahdelsol]

very odd... do the file names start with whats on cell A14? is A14 contain any special characters extra spaces that need to be taken care of...???

They do and there is no special character or anything like that. Yes I use the number that exist in the file name. Also for the test I put in A14 a file name that doesn't exist to see what happens. It searches and searches and then the excel crashes. I even tried to rename the file in case there was some extra character there. Just to add in case it matters I put your code under selection change:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 

[Truiz]

I don't think that is whats causing the error. Honestly I ran out of ideas the code I sent was tested and was working for me not sure why it does not work for you