CODEWITHSUNDEEP
excelvba
Arshit patel
Arshit patel

Reputation: 133

Open workbook with dynamic name

I have to download one workbook every day.

The name of the workbook always starts with CMVOLT.

Whole file name is CMVOLT_22112019.

22112019 represents date and changes daily.

I open this workbook as follows:

Sub openwb()

Dim sPath As String, sFile As String
Dim wb As Workbook

sPath = "C:\Users\Windows\Downloads\"
sFile = sPath & "CMVOLT_22112019.CSV"

Set wb = Workbooks.Open(sFile)

End Sub

So I need to change workbook name daily.

Is there a way to select sheet starting with CMVOLT with VBA?

I will have only one sheet at a time starting with CMVOLT.

Upvotes: 0

Views: 3524

Answers (3)

Supertramp
Supertramp

Reputation: 11

    Dim sDate as String
    sDate = Format(now, "ddmmyyyy")
    sFile = sPath & "CMVOLT_" & sDate & ".CSV"

Upvotes: 1

Igelaty
Igelaty

Reputation: 89

You can write the system-date with the date-function into a string variable and manipulate after it this variable a way, you get it into the format of your date in the filename. Then you use the variable the way like the path and open your file.

Upvotes: 0

braX
braX

Reputation: 11755

If you are sure there will be at least one and only one CSV file using that naming convention in the folder, you can use this

Sub openwb()

  Dim sPath As String, sFile As String, sWild As String
  Dim wb As Workbook

  sPath = "C:\Users\Windows\Downloads\"
  sWild = sPath & "CMVOLT_*.CSV"
  sFile = sPath & Dir(sWild)

  Set wb = Workbooks.Open(sFile)
End Sub

Upvotes: 2

Related Questions