0
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim strPath As String
Dim strName As String

strPath = Environ("USERPROFILE") & "\Desktop\REQUIRED FILES\ART REPORT"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

For Each objFile In objFolder.Files
If objFile.DateLastModified > varDate Then
strName = objFile.Name
End If
Next

Application.Workbooks strPath.strName.Open, vbNormalNoFocus

The above code is to check the folder (strPath) for the latest file saved, store the file name in strName and then open that workbook.

I can't seem to find how i'm supposed to refer to the said file using the variables.

Any help would be much appreciated. Thanks

EDIT: Referring to the answers in the following link didn't solve my problem. I keep getting Error 1004 Other Question

braX
  • 11,506
  • 5
  • 20
  • 33
Nick Razzleflamm
  • 41
  • 8
  • Can you debug your macro and verify the value of `strPath`? I see you are using `&` for concatenating two strings, but I believe that `&` is a worksheet operator. I believe that within VBA you need to use `+` for concatenating strings. (In VBA`&` is a logical operator which gives `FALSE` in case of non-equal entries) – Dominique Dec 14 '18 at 09:00
  • 1
    @Dominique no, use `&`, it concatenates. It is not a logical operator - `And` is. – BigBen Dec 14 '18 at 09:01
  • I'm actually using the same code in a different userform and using srtName to update a label caption. That works fine. I'm having trouble with `Application.Workbooks strPath.strName.Open, vbNormalNoFocus` – Nick Razzleflamm Dec 14 '18 at 09:04
  • @NickRazzleflamm use `Workbooks.Open`. Many examples on SO. Here's [one](https://stackoverflow.com/questions/30833126/excel-vba-open-workbook). – BigBen Dec 14 '18 at 09:04
  • @BigBen `Workbooks.Open strPath.strName, vbNormalNoFocus` throws an 'Invalid Qualifier' error – Nick Razzleflamm Dec 14 '18 at 09:07
  • You need to read the example - here's [another](https://stackoverflow.com/questions/19157385/how-to-open-a-workbook-specifying-its-path). The path and name are the argument - `Workbooks.Open(strPath & "\" & strName)`. – BigBen Dec 14 '18 at 09:08
  • Possible duplicate of [How to open a workbook specifying its path](https://stackoverflow.com/questions/19157385/how-to-open-a-workbook-specifying-its-path) – BigBen Dec 14 '18 at 09:13

1 Answers1

0

Replace the last line Application.Workbooks strPath.strName.Open, vbNormalNoFocus

with this one

Application.Workbooks.Open(strPath & "\" & strName)

For more options you can pass with Workbook.Open, check the MSDN docs

Also what will happen if you have more than one file for which objFile.DateLastModified > varDate? Now you will open the last one in the loop

buran
  • 13,682
  • 10
  • 36
  • 61
  • Hi, thanks for the answer. `Application.Workbooks.Open(strPath & "\" & strName)` throws an error too. Says "Method 'Open' of object 'Workbooks' failed. – Nick Razzleflamm Dec 14 '18 at 09:37
  • And as for having more than one file in the specified folder, I didn't think of that actually. I have the macro deleting all files in the folder before i reach this point in the execution as a safeguard but i guess that isn't productive. – Nick Razzleflamm Dec 14 '18 at 09:42
  • It should work. Check (e.g. Debug.Print) what is the value of `strPath & "\" & strName` – buran Dec 14 '18 at 09:45
  • `C:\Users\shaniked\Desktop\REQUIRED FILES\ART REPORT\ART.xls` Immediate window outputs this.. Which is the correct folder and file name. But still getting the error. – Nick Razzleflamm Dec 14 '18 at 09:51
  • Wait maybe the 'file format doesn't match' error i'm getting when opening ART.xls is keeping the macro from opening it? – Nick Razzleflamm Dec 14 '18 at 09:57
  • As far as I can tell, I don't have problem to open such file (i.e. that display this warning) via VBA. – buran Dec 14 '18 at 10:13
  • Well what do you know i removed the `vbNormalNoFocus` from the line and it works perfectly. But i don't want to lose focus on the active workbook when i open the file. – Nick Razzleflamm Dec 14 '18 at 10:27
  • Sorry, maybe I was not clear to replace the whole line – buran Dec 14 '18 at 10:34
  • Yup. Was a bit confused there. Thanks for your help :) – Nick Razzleflamm Dec 14 '18 at 10:36