Solved

I need help saving/moving my files from one folder into another folder, using ACCESS vba

Posted on 2014-12-18
2
323 Views
Last Modified: 2014-12-24
Hi Experts,
I need help saving/moving my files from one folder into another folder, once I have processed the file. Once the files have been saved/moved into the new folder, they need to be removed from the original folder.  How can i do this?  The code below is what I use to loop through the files in a directory and process the files.
thanks in advance.

Private Sub Command2_Click()
  Const cstrFolder As String = "C:\Schedules\"
  Dim i As Long, x As Long, lng As Long
  Dim xlApp As Object
  Dim xlWrk As Object
  Dim xlSheet As Object
  Dim sql As String
  Dim strExt As String, strFile As String, strTable As String

  
  
  Set xlApp = VBA.CreateObject("Excel.Application")
  xlApp.Visible = False
'
  strExt = ".xls"
  lng = Len(strExt)
  strFile = Dir(cstrFolder & "*" & strExt)

  If Len(strFile) = 0 Then
    MsgBox "No Files Found"
  Else
    Do While Len(strFile) > 0
        'MsgBox (cstrFolder & " - " & strFile)
        'ADD EXCEL CODE HERE
        Set xlWrk = xlApp.Workbooks.Open(cstrFolder & strFile) '("C:\ExcelImportFile.xls")
        Set xlSheet = xlWrk.Sheets("Sheet1")
        

        For i = 11 To 41
            sql = "Insert Into [tblTechAvailability] (Day,Availability,Notes) VALUES ('" & xlSheet.Cells(i, 1).Value & "','" & xlSheet.Cells(i, 2).Value & "','" & xlSheet.Cells(i, 3).Value & "')"
            DoCmd.RunSQL sql
        Next i
        
        xlWrk.Close
        'xlApp.Quit
    
        Set xlSheet = Nothing
        Set xlWrk = Nothing
        'Set xlApp = Nothing
        
        'END EXCEL CODE HERE
       x = x + 1 'KEEPS COUNT OF IMPORTED FILES
       strFile = Dir()
    Loop
    xlApp.Quit
    Set xlApp = Nothing
    
    MsgBox x & " File(s) were imported"
  End If
End Sub

Open in new window


mrotor
0
Comment
Question by:mainrotor
2 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40507246
two ways you can do this

Sub Copy_One_File()
    FileCopy "C:\Users\xName\SourceFolder\Test.xls", "C:\Users\xName\DestFolder\Test.xls"
      
      'delete the original file
      kill "C:\Users\xName\SourceFolder\Test.xls"
      
End Sub

Sub Move_Rename_One_File()
'you change path of the files
    Name "C:\Users\xName\SourceFolder\Test.xls" As "C:\Users\xName\DestFolder\TestNew.xls"
End Sub
0
 
LVL 57
ID: 40507286
and for deleting, there's KILL.

Jim.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MsgBox 4 42
Excess tables to Excel BackUp 3 27
Getting the Error "User-defined type not defined" in MS Access 2013 16 37
RDBMS and No sql database 4 38
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now