Export to Excel when password protected

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

expected format 3
Export SQL 1
Export OLE 1
Loop statements 5
Help with some code Please 1
export file to excel in microsoft access 1
Export Query to Excel in VB 1
Export to Excel - Access 2013 to Excel 2013 0

Top