Comments on Alex & Access: How to import password-protected excel spreadsheet...

Hi,i would try the following - make a new dummy ex...

2006-04-14T08:20:00.000+04:00

Hi,
i would try the following - make a new dummy excel file, a new dummy table to import this excel file, and them after your DoCmd.TransferSpreadsheet each time import this dummy excel file.
Seems like Access/Excel holds some reference to last imported file, so it does not allow to close excel
Also you can try to import dummy text file.

The errors differ. Sometimes Access crashes with a...

2006-04-13T23:57:00.000+04:00

The errors differ. Sometimes Access crashes with a Runtime error. Other times it says that a particular file is being reserved. After doing some more digging, it seems that the problem stems from this part of the code:

oExcel.Quit
Set oExcel = Nothing

Excel never seems to shut down afterwards. If I go into Task Manager and kill any excel.exe processes, then the macro works.
Otherwise the files seem to remain locked in memory and the macro fails.

Thanks again for your help.

Hi,and what error you get when it does not work?

2006-04-13T23:21:00.000+04:00

Hi,
and what error you get when it does not work?

Hi,I found this on google as well and it was a hug...

2006-04-13T23:07:00.000+04:00

Hi,

I found this on google as well and it was a huge help. I needed to modify it to create a loop that opens and import ranges from a series of spreadsheets. The problem is that some times the code works and other times it doesn't. Can you understand why? The problem seems to be with passing in the password string. Something it works and other times it does not. I appreciate any advice that you can give.

Sub CompSheetsEurope_Extract()

On Error GoTo CompSheetsEurope_Extract_Err

Dim recFilePaths As Variant, varFileName As Variant
Dim strPassword As String
Dim db As Database
Dim oExcel As Object, oWb As Object

Set oExcel = CreateObject("Excel.Application")

DoCmd.SetWarnings False

Set db = CurrentDb()
Set recFilePaths = db.OpenRecordset("tblCompSheetsEurope")

strPassword = "comp"

DoCmd.RunSQL "DELETE * FROM [tblSalesActualsQuotas] WHERE [LOB] = 'Europe'"

recFilePaths.MoveFirst

Do
varFileName = recFilePaths![FilePathName]

Set oWb = oExcel.Workbooks.Open(Filename:=varFileName, ReadOnly:=True, Password:=strPassword, _
UpdateLinks:=0, IgnoreReadOnlyRecommended:=True)
DoCmd.TransferSpreadsheet acImport, 8, "tblSalesActualsQuotas", _
varFileName, True, "SalesReport"
recFilePaths.MoveNext
oWb.Close SaveChanges:=False
Loop While Not recFilePaths.EOF

DoCmd.RunSQL "DELETE * FROM [tblSalesActualsQuotas] WHERE ([Actuals] = 0) AND ([Quotas] = 0"

DoCmd.SetWarnings True

CompSheetsEurope_Extract_Exit:
recFilePaths.Close
oExcel.Quit
Set oExcel = Nothing

Exit Sub

CompSheetsEurope_Extract_Err:
MsgBox Error$
Resume CompSheetsEurope_Extract_Exit

End Sub

Hi,I just found this on google. Thank you so much....

2005-04-19T21:29:00.000+04:00

Hi,

I just found this on google. Thank you so much. It was really helpful. I made a few changes to accomodate the specific worksheet and range.

Public Sub ImportProtected(strFile As String, _
strPassword As String)
Dim oExcel As Object, oWb As Object
Set oExcel = CreateObject("Excel.Application")
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, _
Password:=strPassword)

DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=5, _
tablename:="tmpTableName", FileName:=strFile, _
Hasfieldnames:=True, Range:="Q1!C:G"

oWb.Close SaveChanges:=False
oExcel.Quit
Set oExcel = Nothing
End Sub

Thanks Again!