using macro to open a csv file with a file name that changes

tuckercs

New Member
Joined
Mar 1, 2019
Messages
3
I am trying create a macro that will open a .csv file. The file name changes daily. The file name is YYYYMMDD_7dat.csv. Is there a formula that I can put in VBA that will format the open from to this format?
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,276
To specify the current date's file:
Code:
"C:\path\to\folder\" & Format(Date, "YYYYMMDD") & "_7dat.csv"
 

tuckercs

New Member
Joined
Mar 1, 2019
Messages
3
Here is a copy of the macro I am trying to run. All the red text is the text I need to adjust with the current date.
Sub Transfer()
'
' Transfer Macro
'


'
ActiveWorkbook.Queries.Add Name:="20190304_7dat", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""C:\Users\C Tuck\Documents\DATREPORTS\20190304_7dat.csv""),[Delimiter="","", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""SKU"", type text}, {""Qty" & _
" Sold"", Int64.Type}, {""ASIN"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=20190304_7dat;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [20190304_7dat]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "_20190304_7dat"
.Refresh BackgroundQuery:=False
End With
End Sub

What would I need to substitute for this in the macro.

Thanks in advance.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,790
Messages
5,470,793
Members
406,724
Latest member
BruceLee Nguyen

This Week's Hot Topics

Top