Results 1 to 8 of 8
  1. #1
    Join Date
    May 2015

    Unanswered: Linking text file with todays date in file name

    Hey -

    Needing some assistance. I have a text file that is dropped into a folder on a daily basis; each day the text file has todays date in the name, example: CCP2_Urgent_Inquiry_CMS_Rpt05_06_2015.txt. I have a macro with a query that runs daily and utilizes this file, which I have linked into my db. The way I currently do this is by moving the file into my C:/temp folder and removing the date portion of the name and having the text file linked into the db, example: CCP2_Urgent_Inquiry_CMS_Rpt.
    I have to manually move the file to the temp folder and manually remove the date each morning. What I'm hoping is possible is that I can avoid the moving of the file and the removal of the date; I would like to be able to automatically Link or Import this file daily as is with the date included.

    Another option I was thinking about is possibly some automated task that goes out to this folder with the daily drop and moves the CCP2... file with the date to the temp folder and removes the date automatically. Just thinking out loud though.

    I'm pretty good with Ms Access and SQL coding but have limited knowledge of VBA which I'm guessing this is going to involve. I would appreciate any assistance in this matter.


  2. #2
    Join Date
    Oct 2014
    Provided Answers: 6
    Sounds like you are pretty bright.

    Check out Task Scheduler, you can use bat files to call VBA to automate.

    I am cheap and use task scheduler to run jobs with the SQLCMD from the command prompt on instances of SQL Express. I use this to run jobs and to backup my databases.

  3. #3
    Join Date
    Oct 2014
    Provided Answers: 6
    Found this site as well, check it out it maybe what you are looking for.

  4. #4
    Join Date
    May 2015
    Thanks for your responses, but I'm still not finding the answers I am needing through those channels. The main issue being that I need to link in a text file that updates with a different file name daily. Either looking for a way to change that file name upon moving or set up a way in ms access to link to a file with a different name daily. Sorry for being repetitive.

  5. #5
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    or use vbscript and a file system object to read any file of the specified type (so the actuakl filename is less important than the file extension)
    do what ever processes you need to then delete the file afterwards (assuming the processes have terminated successfully)
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2014
    Provided Answers: 6

    Get a 3rd party tool that copies the file from the root location and drops it in the location you are linking to now, you can also rename it. There are utilities that can assist with this automation.

    You might be able to copy to two locations one for record and the other for the upload, such as a repository etc.
    Last edited by VLOOKUP; 05-14-15 at 15:15.

  7. #7
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 8
    as u know folder name and the filename

    Here what about this

    sub My_Relink()
    Dim FileName As String
    Dim NewName As String
    FileName = "CCP2_Urgent_Inquiry_CMS_Rpt[dd]_[mm]_[yyyy].txt"
    NewName = Replace(FileName, "[dd]", Format(Day(Date), "00"))  ' so it has a leading 0
    NewName = Replace(NewName, "[mm]", Format(Month(Date), "00")) ' so it has a leading 0
    NewName = Replace(NewName, "[yyyy]", Year(Date)) 
    ' I like to use the replace() function make it easyer to read.
    TableNameis = "thisname"
    workingFile = "C:\fold\inthisfold\" & NewName
    if fileExists(workingfile) then
    'now we have to delete the table so we dont get the 1 added to it
    DoCmd.DeleteObject acTable,TableNameis
    'now lets create the link
    DoCmd.TransferText acLinkDelim, , TableNameis, workingFile, True
    msgbox("can't find the file " & vbnewline & vbnewline & vbnewline & workingFile)
    End if
    End Sub
    Function fileExists(s_fileName As String) As Boolean
    'check if a file exits in the folder
    'INPUT : full path of file
    'RETURN: true or false
        Dim obj_fso As Object
        Set obj_fso = CreateObject("Scripting.FileSystemObject")
        fileExists = obj_fso.fileExists(s_fileName)
    End Function
    Last edited by myle; 05-14-15 at 22:37.
    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

  8. #8
    Join Date
    Oct 2014
    Provided Answers: 6
    LOL - How many solutions can you provide. Haha

    Done with this one, at a certain point if you lack the skill level you might consider taking a course on scripting or windows automation.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts