How do I pull data from a file that is changing names?

bdkrame

New Member
Joined
Aug 26, 2015
Messages
9
Hello,

I am trying to pull data from a file that is live updating as it is used, but the file saves as a new name every 24 hours. How do I write in a name to open that allows the macro I have to continue pulling data but pull from the data for the current date?

For reference, every time a machine is run it produces and saves one line of results onto an existing .csv file. This file is named with the day and machine name, so it looks something like "20151130_MachineName.csv" This would be an example of today's file. Tomorrow (December 1st) would have a file name "20151201_MachineName.csv" I want my macro to always be pulling from the file from the current day.

My code works for pulling an individual day right now, but I have to manually change it to the new day. Here's what I've got:

Code:
Sub Get_Data()    Application.Worksheets("Data").Unprotect
    Workbooks.OpenText FileName:="K:\Keyence\20151130IM_MachineName.csv", _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
    Range("A1:X255").Select
    Selection.Copy
    ThisWorkbook.Activate
    Sheets("Data").Select
    Range("A1").Select
    ActiveSheet.Paste

Any help would be greatly appreciated. Thanks!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,180
Change 2nd line from
Workbooks.OpenText FileName:="K:\Keyence\20151130IM_MachineName.csv", _

to
Workbooks.OpenText FileName:="K:\Keyence\" & FORMAT(Date,"yyyymmdd") & "IM_MachineName.csv", _
 

bobgentry

New Member
Joined
Nov 25, 2015
Messages
46
well, you gotta change the filename string to a string variable, and make a function to change the string appropriately.
since you know the rules for the name change, it should be simple.

filename portion changed to something like Filename:=GetCurrentFN(machine_name)

and then somewhere make a function:
Code:
function GetCurrentFN(machine_name as string)

    dim tmpStr as string
    tmpStr = Date
    
    GetCurrentFN = tmpStr & machine_name 

end function
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,615
Members
412,333
Latest member
hectamuptra
Top