Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-12-18
2
Medium Priority
?
338 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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 58
ID: 40507286
and for deleting, there's KILL.

Jim.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

609 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