Edit Macro to Open Specific File Automatically

[erock24]

I am using this code to get me to a path then I click on the file I want and it opens it and makes it a .csv file. Then my code does stuff with it and closes it. My question is, do I even have to click on it? Can I get the macro to automatically open the file. Note the "NewestFolderInThePath" is a function that open me up to the "newest" created folder in the path. From there I double click file.

NewestFolderInThePath = ReturnNewestFolder("\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\")
   Path = "\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\"
    
    TheFile = Path & NewestFolderInThePath
    CreateObject("WScript.Shell").CurrentDirectory = TheFile
    
    TheFile = Application.GetOpenFilename("Excel Files (*.*), *.*", , "Open Balance Sheet File FRPMBSDTL")
If TheFile = "False" Then
      continue = MsgBox("Do you want to Continue to IS Download?", _
                    vbInformation + vbYesNo, _
                    "Continue?")

If continue = 6 Then GoTo IncSt
If continue = 7 Then Exit Sub
End If
      Oldfilename = TheFile

    ' Separate the file path and file name
      BS = InStrRev(Oldfilename, "\")
      Oldfilepath = Left(Oldfilename, BS)
      Newfilename = Right(Oldfilename, Len(Oldfilename) - BS)
    
    ' Add the CSV extension to the file name
      Dot = InStr(1, Newfilename, ".")
      Newfilename = Oldfilepath & Left(Newfilename, Dot) & "csv"
    
    ' Rename the file
      Name Oldfilename As Newfilename

    Workbooks.Open Filename:=Newfilename
     
    Set OpenA = Workbooks(Workbooks.Count)

 

[erock24]

is this not doable

 

[Andrew Poulsom]

Do you know the name of the file?

 

[erock24]

Yes. it is SSFRPMBSDTL

 

[Andrew Poulsom]

So can't you just add it to:

TheFile = Path & NewestFolderInThePath

like so?

TheFile = Path & NewestFolderInThePath & "SSFRPMBSDTL"

 

[erock24]

No it errors out at:

CreateObject("WScript.Shell").CurrentDirectory = TheFile

I was getting the same error to my current macro before I added this:

Path = "\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\"

So maybe we need to account for the file name. set it = to something then reference it something like this:

File = ????????
TheFile = Path & NewestFolderInThePath & File

I don't know. I'm obviously not an expert by any means.

 

[Andrew Poulsom]

You don't need that line if you don't want to prompt for the file name.

 

[erock24]

it's still not quite there yet.

But I found the problem
I put

msgbox(TheFile)

after

TheFile = Path & NewestFolderInThePath & "SSFRPMBSDTL"

it returns

\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\895993994SSFRPMBSDTL.

It is not separating out the folder name 895993994 and the file name SSFRPMBSDTL.
How do I fix that?

 

[Meader693]

could you not add a forward slash to your code:

TheFile = Path & NewestFolderInThePath & "\SSFRPMBSDTL"

 

[erock24]

Meader693,
I'm not sure if that is working;

I think I need to change this part also because it is prompting me to open a file:

TheFile = Application.GetOpenFilename("Excel Files (*.*), *.*", , "Open Balance Sheet File FRPMBSDTL")

To:

Just Open "TheFile". How do I say open "TheFile"