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.