Linking to a csv file that is named differently each day 2

[JustineB]

I hope that somone can help me.

I have a text file (csv) that I currently link to using an access database. This data is then manipulated and specific rows are exported. All is working well when I test the database and everything works as expected.

Unfortunatley, when I was given the original requirement, the person for whom I am doing this work failed to tell me that the text file has a different name each day. It is basically a file name with the date as a 6 digit number appended to the file name. The other issue that I have to deal with is that the folder where the file is stored also changes on a daily basis to reflect the file name change.

I was using the standard link table manager to originally link to this csv file. As the file name is not dynamic, I can no longer use this method of linking.

With this in mind, would someone be able to provide sample code / point me in the right direction so that I can create a way of either asking the user to select the file (not ideal) or to find a way of searching for the latest folder / file and link automatically to that?

I am stumped at the moment so any assistance or ideas would be great!

Many thanks,
Justine

 

[Remou]

It is easy enough, I think, to get the file name:

[tt]strFileName="ABC" & Format(Date,"yymmdd") & ".txt"[/tt]

How is the the folder name changed?

PS I remember this coming up before, but cannot recall the thread.

 

[JustineB]

Hi Remou

Many thanks for the help. I will have a play with it. Can you let me know how I link to the Filename? I need to link to the csv file to then run a series of queries. This in itslef presents another issue as the linked filename will have changed, therfore the query will not find the table - I am thinking that I will need to rename the linked table to a general name and use this as the table name so that the queries etc will not be affected.

I will let you know how I get on - any further assistance will be much appreciated!

Many thanks again.
Justine

 

[Remou]

The idea of renaming the file seems good. You can use the Name statement:

Name oldpathname As newpathname

You can use DoCmd.TransferText to link the table, but first use DoCmd.DeleteObject to get rid of the older copy. You may wish to test that an older copy exists before deleting.

 

[JerryKlmns]

Find the latest file in a specific folder

Function myFileSearching(ByVal File_Name As String, ByVal Search_Folder As String) As String

With Application.FileSearch
    .NewSearch
    .FileType = 1 'msoFileTypeAllFiles
    .Filename = File_Name & "*.txt"
    .LookIn = Search_Folder
    .SearchSubFolders = False
    If .Execute(4, 1, True) > 0 Then 'msoSortByLastModified,msoSortOrderAscending
        myFileSearching = .FoundFiles(.FoundFiles.Count)
    Else
        MyFile = "Not Found"
    End If
End With
End Function

 

[JustineB]

Thanks so much for your help both of you! I will lest and get back to you with how I get on!

Justine

 

[JustineB]

People just don't make things easy for you do they? Our files have a 'dot' between the name eg: 25.09front.txt (Arghhh!)

When I remove the dot (I will strip this out using code), the text is transferred, but not in it's entirety, only the first column is transferred. can you suggest what I am doing wrong?

Here is my (very basic!) code so far:

Sub getfilename()

Dim strfilepath As String
Dim strfoldername As String
Dim strfilename1 As String
Dim strfilename2 As String
Dim strfilename3 As String
Dim strfilename4 As String
Dim strfile1 As String
Dim Strfile2 As String
Dim strFile3 As String
Dim strFile4 As String


strfoldername = Format(Date, "yyyy") & "-" & Format(Date, "mm") & "-" & Format(Date, "dd")
strfilepath = "C:\CSL Data\idcards\Incoming Data\" & strfoldername
strfilename1 = Format(Date, "dd") & "." & Format(Date, "mm") & "back.txt"
strfilename2 = Format(Date, "dd") & Format(Date, "mm") & "front.txt"
'strfilename2 = Format(Date, "dd") & "." & Format(Date, "mm") & "front.txt"
strfilename3 = Format(Date, "dd") & "." & Format(Date, "mm") & "pri_back.txt"
strfilename4 = Format(Date, "dd") & "." & Format(Date, "mm") & "pri_front.txt"

strfile1 = strfilepath & "\" & strfilename1
Strfile2 = strfilepath & "\" & strfilename2
strFile3 = strfilepath & "\" & strfilename3
strFile4 = strfilepath & "\" & strfilename4

'Name strfilename1 As "back.txt"
'Name strfilename2 As "Front.txt"
'Name strfilename3 As "Pri_Back.txt"
'Name strfilename4 As "Pri_Front.txt"

DoCmd.TransferText acImportDelim, , "front", Strfile2, True


End Sub

 

[Remou]

What does the data look like? One or two mock-up lines would be good.

 

[JustineB]

Hi Remou

I have figured it out by using a Specification.
My last line of code now reads:

DoCmd.TransferText acImportDelim, "front import specification", "front", Strfile2, True

Many thanks to both of you for pointing me in the right direction - you can both have a star

Justine

 

[JerryKlmns]

A litlle more info, just for you

Format(Date, "dd") & "." & Format(Date, "mm") = Format(Date, "dd.mm"), just put that in a variable and reduce calls to functions (not a very source intenseve in your case, but just to let you know )

Format(Date, "yyyy") & "-" & Format(Date, "mm") & "-" & Format(Date, "dd") = Format(Date, "yyyy-mm-dd")

Thanx for the *

 

[JustineB]

Many thanks for that! sometimes I am just plain stupid!