mainrotor
asked on
I need help saving/moving my files from one folder into another folder, using ACCESS vba
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.
mrotor
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
mrotor
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jim.