[AccessD] Loadfromtext

Hale, Jim Jim.Hale at FleetPride.com
Mon Sep 25 15:01:42 CDT 2006


Here is how I do it HTH
Jim Hale
Public Function UploadPlan(Filespec As String) As Boolean
'Retrieves Plans from Excel workbooks
Dim filecount As Integer, i As Integer
  Dim fm As Form
  DoCmd.SetWarnings False
 Set fm = Forms!frmselect
'   Get file spec
    newpath = Pathnamefile(Filespec)
    newpath = newpath & "\Completed\" 'add location of completed directory
'   Extract the path and change directory
   If newpath <> "" Then
        On Error Resume Next
        ChDir newpath
            If Err.Number = 76 Then
                MsgBox "Directory " & newpath & " is not valid", 16, "Error"
                Exit Function
            End If
        Else: Exit Function
    End If
    On Error GoTo Err_UploadPlan
    DoCmd.Hourglass True
'   See if any files exist
    foundfile = Dir(newpath)
    
    If foundfile = "" Then
        MsgBox "Cannot find file:" & Chr(13) & Filespec
        DoCmd.Hourglass False
        Exit Function
        
    End If

'   Get first file name
    filecount = 1
    ReDim Preserve Files(filecount)
    Files(filecount) = foundfile

'   Get other file names, if any
    Do While foundfile <> ""
        foundfile = Dir()
        If foundfile <> "" Then
            filecount = filecount + 1
            ReDim Preserve Files(filecount)
            Files(filecount) = foundfile
        End If
    Loop
'If 1 = 2 Then
If Excel_is_running = True Then
    Set appexcel = GetObject(, "Excel.Application")
Else
    Set appexcel = New Excel.Application
End If
'   Loop through all files and process them
    For i = 1 To filecount
        fm.lblPathname.Visible = True
        fm.lblPathname.Caption = "Processing: " & Files(i)
        fm.Repaint
        
       'Call procedure that opens workbooks and extracts Budget
       Call ProcessFiles(newpath, Files(i))
       
   Next i
   UploadPlan = True
   fm.lblPathname.Visible = False
The_End:
DoCmd.SetWarnings True
    appexcel.Quit
    If Not (appexcel Is Nothing) Then Set appexcel = Nothing
    If Not (rstbase Is Nothing) Then rstbase.Close: Set rstbase = Nothing
    If Not (dbs Is Nothing) Then dbs.Close: Set dbs = Nothing
    If Not (fm Is Nothing) Then Set fm = Nothing
    DoCmd.Hourglass False
 Exit Function
Err_UploadPlan:
    MsgBox Err.Description
    UploadPlan = False
    Resume The_End
    
  End Function

-----Original Message-----
From: Martin Reid [mailto:mwp.reid at qub.ac.uk]
Sent: Monday, September 25, 2006 10:28 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Loadfromtext


I want to loop over a folder contain X number of text files. Any ideas. For
example
 
Application.LoadFromText acForm, "test", "C:\forms\Form_Customer List.txt"

Instead of a single file I need them all BUT some are prefixed FORM,
REPORT,MODULE, QUERY and I need to change the constant accordingly.
 
I know I can run one process for each type but would it be possible to do
them all in the proper squence in one hit.
 
Martin
 
Martin WP Reid
Training and Assessment Unit
Riddle Hall
Belfast
 
tel: 02890 974477
 
 


***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.


More information about the AccessD mailing list