A
Ayyad K
I have this module set up to import all the excel files from a certain
folder, and since these files are protected, i had to place some extra
code in there to open unprotect import and close each excel file it
wants to import...
But the excel is for some reason still running in the background which
causes those particular excel files to only be read only... unless you
ctrl alt del and force excel off from the processes... Any ideas on
that??
Now The second issue is that i setup that do loop in order to reiterate
throughout the folder and import the range from all the xls files there
But lets say I have that user list table with the fields of 'Name',
'Active' (a checkbox), and 'FileName' pretaining to each user
how can i place conditions within my current code that would only
import any user that is active on that table (somehow ofcourse using
their assosciated excel FileName') from that same table
here is the code...
code:
-------------------------------------------------------------------
Option Compare Database
Public xlapp As New Excel.Application
Public Sub ImportAll()
Dim strPath As String
Dim strFileName As String
Dim strPass As String
strPath = "G:\C\B\T\" 'Set Path
strFileName = Dir(strPath & "*.xls") 'Set first file
Do
On Error GoTo ErrTrp
DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath &
strFileName, True, "Access_Upload!C13:L34"
ErrTrp:
If Err.Number = 3161 Then 'Encription
error so unprotect workbook
xlapp.Visible = False 'Open Excel
xlapp.EnableEvents = False 'Disable Events
(Macro's)
xlapp.workbooks.Open strPath & strFileName 'Open File
xlapp.ActiveWorkbook.Unprotect (strPass) 'Unprotect
'Try and Import again
DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath &
strFileName, True, "Access_Upload!C13:L34"
xlapp.ActiveWorkbook.Protect (strPass) 'protect
xlapp.ActiveWorkbook.Save 'Save
xlapp.EnableEvents = True 'Enable Events
xlapp.ActiveWorkbook.Close 'Close File
xlapp.Quit 'Quit
Excel
Else
End If
strFileName = Dir() 'look for next file
If strFileName = "" Then 'no more files
Exit Do
End If
Loop
End Sub
-------------------------------------------------------------------
[End Code]
I would appreciate any assistance
Ayyad
folder, and since these files are protected, i had to place some extra
code in there to open unprotect import and close each excel file it
wants to import...
But the excel is for some reason still running in the background which
causes those particular excel files to only be read only... unless you
ctrl alt del and force excel off from the processes... Any ideas on
that??
Now The second issue is that i setup that do loop in order to reiterate
throughout the folder and import the range from all the xls files there
But lets say I have that user list table with the fields of 'Name',
'Active' (a checkbox), and 'FileName' pretaining to each user
how can i place conditions within my current code that would only
import any user that is active on that table (somehow ofcourse using
their assosciated excel FileName') from that same table
here is the code...
code:
-------------------------------------------------------------------
Option Compare Database
Public xlapp As New Excel.Application
Public Sub ImportAll()
Dim strPath As String
Dim strFileName As String
Dim strPass As String
strPath = "G:\C\B\T\" 'Set Path
strFileName = Dir(strPath & "*.xls") 'Set first file
Do
On Error GoTo ErrTrp
DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath &
strFileName, True, "Access_Upload!C13:L34"
ErrTrp:
If Err.Number = 3161 Then 'Encription
error so unprotect workbook
xlapp.Visible = False 'Open Excel
xlapp.EnableEvents = False 'Disable Events
(Macro's)
xlapp.workbooks.Open strPath & strFileName 'Open File
xlapp.ActiveWorkbook.Unprotect (strPass) 'Unprotect
'Try and Import again
DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath &
strFileName, True, "Access_Upload!C13:L34"
xlapp.ActiveWorkbook.Protect (strPass) 'protect
xlapp.ActiveWorkbook.Save 'Save
xlapp.EnableEvents = True 'Enable Events
xlapp.ActiveWorkbook.Close 'Close File
xlapp.Quit 'Quit
Excel
Else
End If
strFileName = Dir() 'look for next file
If strFileName = "" Then 'no more files
Exit Do
End If
Loop
End Sub
-------------------------------------------------------------------
[End Code]
I would appreciate any assistance
Ayyad