Importing text files into access within date range

[VbAaron]

Been searcing for a while, and cannot seem to find a case that is similiar enough to mine.

I want a string of code that allows me to import a series of delimited text files into access using VBA. I have the first bit of code down:

DoCmd.TransferText acImportDelim, deltxtimptbl, "Delivery(local)", "\\msfs3109\data1\share\everyone\prorep\tranhist\Delivery\" & currentdate & ".txt"

The text files hold data for a specific days work. Each day has its own text file. I would like to be able to import the text files within a specific date range, specified by the user. Below is the total code i have for my form button, any help would be greatly appreciated. I'm self taught, so be gentle :

rivate Sub Command0_Click()
Dim startdate As String
Dim enddate As String
Dim currentdatex
Dim count As Integer
count = 0
startdate = Format(Me.Text1, "YYYYMMDD")
enddate = Format(Me.Text3, "YYYYMMDD")
currentdate = startdate
While currentdate <= enddate And count < 7
 DoCmd.TransferText acImportDelim, deltxtimptbl, "Delivery(local)", "\\msfs3109\data1\share\everyone\prorep\tranhist\Delivery\" & currentdate & ".txt"
 count = count + 1
 currentdate = Format((Me.Text1 + count), "YYYYMMDD")
Wend
DoCmd.OpenTable "Delivery(local)", acViewNormal
End Sub

Cheers!

 

[Rand]

what do the filenames actually look like in the directory ie: 10-19-2014.txt

 

[VbAaron]

the file names are: "yyyymmdd.txt" (year, month, day)

 

[namliam]

1) make sure that the Command0, me.text1 and 3 get "proper" names
2) make sure the dates in text1 and 3 are entred by the user in US Format (MM/DD/YYYY)

Bar those two, your code seems to be good to go, other than...
Dim currentdatex
Should be:
Dim currentdate as string

 

[VbAaron]

Private Sub Command0_Click()
Dim startdate As String
Dim enddate As String
Dim currentdate As String
Dim count As Integer
count = 0
startdate = Format(Me.txtstart, "YYYYMMDD")
enddate = Format(Me.txtend, "YYYYMMDD")
currentdate = startdate
While currentdate <= enddate And count < 7
 DoCmd.TransferText acImportDelim, deltxtimptbl, "Delivery(local)", "\\msfs3109\data1\share\everyone\prorep\tranhist\Delivery\" & currentdate & ".txt"
 count = count + 1
 currentdate = Format((Me.txtstart + count), "YYYYMMDD")
Wend
DoCmd.OpenTable "Delivery(local)", acViewNormal
End Sub

That is my code after the changes you offered to me. Thanks very much for the help. Unfortunately, the table that i open contatins no records after import.

What I'm after is allowing the user to pull in the text files that fall within a certain week or date range of seven days to get a proper understanding of team mate habits. Each text file's name corresponds with a date with format 'YYYYMMDD'. The date range is specified with txtstart and txtend, text boxes on a form. The code is run with a button press after dates are entered.

 

[namliam]

Your code is not completely sensical, why have both an end date and the <7 restriction?
one or the other should be enough shouldnt it?

Run this code and post back the information from the immediate window, if you dont know what that is, you can press CTRL + G that will bring it up.

Private Sub Command0_Click()
    Dim startdate As String
    Dim enddate As String
    Dim currentdate As String
    Dim count As Integer
    count = 0
    startdate = Format(Me.txtstart, "YYYYMMDD")
    enddate = Format(Me.txtend, "YYYYMMDD")
    Debug.print Me.txtStart, Startdate
    Debug.print me.txtend, Enddate
    currentdate = startdate
    While currentdate <= enddate And count < 7
    DoCmd.TransferText acImportDelim, deltxtimptbl, "Delivery(local)", "\\msfs3109\data1\share\everyone\prorep\tranhist\Delivery\" & currentdate & ".txt"
        count = count + 1
        currentdate = Format((Me.txtstart + count), "YYYYMMDD")
        Debug.print me.txtstart + count, currentdate
    Wend
    DoCmd.OpenTable "Delivery(local)", acViewNormal
End Sub

Please for your table names and such refer to http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=name+convention, at the very least for future projects.

 

[VbAaron]

I ran the code in the Immediate Pane and it returned: "Compile Error: Invalid in Immediate pane"

I do use object naming conventions, this is just a "rough copy". My goal with all my db's is that on release, anyone can maintain it, as long as they have the knowledge to do so.

I have the loop because I have 5 other tables that I need to populate from text files located elsewhere on our shared drive. I just cant get the one to work....unless the issue is that I have only 1 import so far :banghead:.

 

[namliam]

You cant execute the code in the immediate window, you paste the code in a normal module, run it from there, then get the results from the immediate window

 

[VbAaron]

I get a error window: "Compile Error: Invalid use of Me keyword"

 

[namliam]

Because what does "ME" refer too?

 

[VbAaron]

'Me' is intended to set focus to the two text boxes on the main form where the two date's are entered by the user.

 

[namliam]

Me is not intented to set focus to textboxes....

Me is a reference to the current form where the code is supposed to be executed from

 

[VbAaron]

yeah that! lol Sorry, brain fart. I'm at work dealing with requests and what not. Yeah, the code is embedded on a form, and the 'Me' is intended to reference that form. Is that redundant?