Importing from Multiple Folders (1 Viewer)

JDeezy · Mar 24, 2010

I am trying to create this module or form that imports a specific text file in multiple folders into a table in access 2002. I want to use a date range say from 2/1/10 to 3/3/10 but I cant figure out how to make it skip the missing weekend folders. Below is what I have so far which is only using a single date. The date is also formatted in yymmdd.

Code:

Private Sub cmdConvert17_Click()
Dim strDate As String
Dim SourceFile
    DoCmd.SetWarnings False
strDate = txtDate
    ' ERS Equip Delete
    DoCmd.OpenQuery "ERS Equip Delete", acViewNormal, acEdit
    ' SourceFile
    SourceFile = "G:\SHARED\CSGRJE\" & strDate & "\ERS_EQUIP.TXT"
    ' Transfer ERS_EQUIP
    DoCmd.TransferText acImportFixed, "ERS_EQUIP Import Specification", "ERS_EQUIP", SourceFile, False, ""
    ' ERS Equip Archive Append
    DoCmd.OpenQuery "ERS Equip Archive Append", acViewNormal, acEdit
    DoCmd.SetWarnings True
End Sub

DCrake · Mar 24, 2010

You need to test if strDate is a Sat or Sun, if so ignore the next bit of code and more onto the next record in the table/query.

JDeezy · Mar 24, 2010

Actually I need it to read every day between the date range and if it comes back null then skip it incase there was a folder there I didnt expect on but I do not know how to setup that line of coding.

DCrake · Mar 24, 2010

Lets assume the date range is 01/01/2010 to 01/02/2010

You need to set up a loop

Then you need to set up a temp date

Code:

Dim tDate as Date

tDate = Start Date

For x = 1 to DateDiff("d",End Date,Start Date)
    tdate = DateAdd("d",x,Start Date)
    If Format(tDate,"ddd") <> "Sat" or Format(tDate,"ddd") <> "Sun" Then
       Do something
    Else
       Do Nothing
    End If
Next

Importing from Multiple Folders (1 Viewer)

JDeezy

Registered User.

DCrake

Remembered

JDeezy

Registered User.

DCrake

Remembered

Similar threads

Users who are viewing this thread