VBA: set a dynamic name of a source workbook

[Adar123]

Hi everyone,

I am a newbie in VBA, hence seeking your help with the following code:

Sub copyColData()

' copy data from closed workbook

Dim lastRow As Long
Dim myApp As Excel.Application
Dim wkBk As Workbook
Dim wkSht As Object

Set myApp = CreateObject("Excel.Application")
Set wkBk = myApp.Workbooks.Open("H:\documents\file_2020-07-13_2020-07-14_000501UTC.csv")

lastRow = wkBk.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
wkBk.Sheets(1).Range("C2:H" & lastRow).Copy

myApp.DisplayAlerts = False
wkBk.Close
myApp.Quit
Set wkBk = Nothing
Set myApp = Nothing
Set wkBk = ActiveWorkbook
Set wkSht = wkBk.Sheets("Sheet1")
wkSht.Activate
Range("A1").Select
wkSht.Paste

' delete columns with unnecesary data

Range("B:B,D:D").Delete
Exit Sub


End Sub

The code copies data from a closed source workbook location of which is defined as "H:\documents\file_2020-07-13_2020-07-14_000501UTC.csv". The name of the file which contains the source data contains the date, how would you set the name dynamic e.g. "H:\documents\file_[yesterday in format above]_[today in format above]_000501UTC.csv]?

Each day, I will be downloading a new source file with a name generated in a format above.

Thanks for your advice.

 

[LazyBug]

How about

Dim dTod As String, dYes As String

dTod = Format(Date, "yyyy-mm-dd")
dYes = Format(Date - 1, "yyyy-mm-dd")

Set wkBk = myApp.Workbooks.Open("H:\documents\file_" & dYes & "_" & dTod & "_000501UTC.csv")

 

[Adar123]

This worked! Thank you.

Follow up question: I just realized that the last part of the document name may change to _000502UTC.csv. Is there a way to define a portion of the filename as "anything" (an equivalent of * when used in search)?

I thought of building OR function syntax but haven't gotten my around about defining presence of a file in folder. In addition, a month down the road there may appear a third variation of the ending of the file name, so using OR and IF functions may be not an efficient way to do it.

 

[LazyBug]

Try this.

Option Explicit

Sub copyColData()
    Dim lastRow As Long, myApp As Excel.Application, wkBk As Workbook, wkSht As Worksheet
    Dim dTod As String, dYes As String, WildPath As String
    
        Set myApp = CreateObject("Excel.Application")
        
        dTod = Format(Date, "yyyy-mm-dd")
        dYes = Format(Date - 1, "yyyy-mm-dd")
        WildPath = Dir("H:\documents\file_" & dYes & "_" & dTod & "_00050" & "?UTC.csv")
        
            'Set wkBk = myApp.Workbooks.Open("H:\documents\file_2020-07-13_2020-07-14_000501UTC.csv")
            If WildPath <> "" Then
                Set wkBk = myApp.Workbooks.Open(WildPath)
            Else
                MsgBox "Out there."
                Exit Sub
            End If
            
            With wkBk
                lastRow = .Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
                .Sheets(1).Range("C2:H" & lastRow).Copy
            End With
        
        myApp.DisplayAlerts = False
        wkBk.Close
        myApp.Quit
                
            Set wkSht = ActiveWorkbook.Sheets("Sheet1")
            wkSht.Range("A1").Paste
            wkSht.Range("B:B,D:D").Delete
    
    'Exit Sub
End Sub

 

[Adar123]

I get 1004 error saying cannot find the file and pointing to line Set wkBk = myApp.Workbooks.Open(WildPath). The CSV file is definitely in the folder and VBA picks its correct name.

 

[LazyBug]

I get 1004 error saying cannot find the file and pointing to line Set wkBk = myApp.Workbooks.Open(WildPath). The CSV file is definitely in the folder and VBA picks its correct name.

Could you copy and paste here full path and name of your source workbook with extension?

 

[LazyBug]

Maybe (not tested)?

            If WildPath <> "" Then
                wkBk = myApp.Workbooks.Open(WildPath)
            Else
                MsgBox "Out there."
                Exit Sub
            End If

 

[Adar123]

I did the following test:

1. If there is no file with the specified name in the folder, I get "Our There" message.
2. If there is a file with the specified name in the folder, I get the "1004" message.
3. There is no difference in the results between the original code and the one without "set" in front of

   wkBk = myApp.Workbooks.Open(WildPath)

Here is a full path to the document:

H:\documents\1. Trading\Excel\DumpFolder\DataX_CM_FUT1_2020-07-23_2020-07-24_000502UTC.csv

(note the dates will be different each day)

The Wildpath included in the macro:

WildPath = Dir("H:\documents\1. Trading\Excel\DumpFolder\DataX_CM_FUT1_" & dYes & "_" & dTod & "_00050" & "?UTC.csv")

 

[LazyBug]

H:\documents\1. Trading\Excel\DumpFolder\DataX_CM_FUT1_2020-07-23_2020-07-24_000502UTC.csv

This would work if today was july 24nd.
Macro will only work for files with name ended with [yesterday in format above]_[today in format above]_00050[one character]UTC.csv, as you asked in #3.

 

[Adar123]

Picking this theme again, have you tested this method with extracting data? It identifies data at the stage of "If" test but throws out run-time error '91': "Object Variable or With Block variable not set" when reach .Namespace(destFolder).CopyHere .Namespace(localZipFile).Items

Any thoughts?

Dim localZipFile As Variant, destFolder As Variant  'Both must be Variant with late binding of Shell object
Dim Sh As Object

'Dim strFileName As String
'Dim strFileExists As String

'Check the file name ending
strFileName = "drive\path\file_" & dYes & "_" & dTod & "_0" & "?0501UTC.zip"
strFileExists = Dir(strFileName)
 
If strFileExists <> "" Then     '.zip file to be unzipped
    localZipFile = "drive\path\file_" & dYes & "_" & dTod & "_0" & "?0501UTC.zip"
Else
    MsgBox "File Not Found in Folder"
End If
    
destFolder = "drive\path\"    'destination folder of .zip file's unzipped contents
    
'Unzip all files in the .zip file
        
Set Sh = CreateObject("Shell.Application")
With Sh
        
.Namespace(destFolder).CopyHere .Namespace(localZipFile).Items
End With