Export to Excel when password protected

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

Hi,

I have a button on a form that exports queries to an Excel file. This
worked perfectly until the Excel file needed to be password protected. I
have managed to pull together the code for this but it stops working when
Excel file opens. Here is the code:

Private Sub ProducePack_Click()

Dim strFile As String
Dim strPassword As String

Dim sMsg As String, sFilePath As String
Dim vItem
Dim SourceFile, DestinationFile

Dim oExcel As Object, oWb As Object

DoCmd.SetWarnings False

strPassword = "rbsg"

Call CurrentDBDir

SourceFile = CurrentDBDir & "AD Pack.xls"
DestinationFile = "c:\Temp\AD Pack.xls"
FileCopy SourceFile, DestinationFile

sMsg = "Completed!"
ubADName = Null
intProgress = 0

For Each vItem In Me.ADName.ItemsSelected
ubADName = Me.ADName.ItemData(vItem)

SourceFile = "c:\Temp\AD Pack.xls"
DestinationFile = "c:\temp\" & Me.ubADName & ".xls"
FileCopy SourceFile, DestinationFile

Set oExcel = CreateObject("Excel.Application")
Set oWb = oExcel.Workbooks.Open(Filename:=DestinationFile,
Password:=strPassword)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Report
01", DestinationFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Report
01_1", DestinationFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Report
02", DestinationFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Report 02
List", DestinationFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Report
03", DestinationFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Report
04", DestinationFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Report
06", DestinationFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Report 06
List", DestinationFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Report 06
Summary", DestinationFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Report
08", DestinationFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Report
09", DestinationFile, True
DoCmd.OpenQuery "Report 10", acViewNormal
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Report
10_1", DestinationFile, True

oWb.Close SaveChanges:=False
oExcel.Quit

Set oExcel = Nothing

Me.intProgress = Me.intProgress + 1
Me.Repaint
Next

Call DoList("C", "ADname")

DoCmd.SetWarnings True

Kill "c:\Temp\AD Pack.xls"

Call Shell("C:\WINDOWS\EXPLORER.EXE ""c:\Temp""", 4)

End Sub

I would like to know how to get around the problem of opening Excel, I think
I don't actually need it open in it's true sense, just open up to get around
the password problem, export the queries and then close.

Can anyone help?

Many thanks,

Martin
 
Excel opens, I am prompted to enter the password manually and then it says
that Report 01 already exists. This happens just becuase the file is open.
If it isn't open then it overrights the Report 01 data as I want it to do.

Thanks,

Martin
 
Back
Top