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!
 

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,077
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
 

Forum statistics

Threads
1,082,253
Messages
5,364,045
Members
400,776
Latest member
JimmyLee

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top