Exporting to Excel 1

[GLENEE]

I've exported a recordset to Excel by code and then wish to view the spreadsheet and allow the user to edit the data. The spreadsheet is based on a template which has its worksheets protected (to prevent alterations )and then saved as another file. I use the following code to open the spreadsheet from Acess:
[Set objExcel = GetObject(STRFILENAME)
objExcel.Application.WORKSHEETS(1).Unprotect "password"
objExcel.Application.Visible = True]
All this is fine and the user can make changes, however when the user closes the spreadsheet and opens it at another time the sheet is automatically protected. Does unprotecting from access only work the once, how do i permanatley unprotect the spreadsheet (without telling the user the password!)

 

[dazbc]

Im trying to do the same thing, can you tell me how you exported the results of the recordset into excel?

 

[GLENEE]

dazbc
This is a sample/cut of the code i used to export to Excel

[Public Function MakeSAReport(ByVal strTemplate, strFileName As String)
Dim dbTemp As Database
Dim objExcel As Object
Dim intRow As Integer

Set dbTemp = CurrentDb()

'GET SOURCE DATA TO EXPORT
strSQLF = "select * from qryBenefitExport where [intPeriodSequence] = " & PerSeqBen()

‘NAME/PATH OF TEMPLATE.XLS PASSED IN FROM ANOTHER FUNCTION
Set objExcel = GetObject(strTemplate)

Set rstTempF = dbTemp.OpenRecordset(strSQLF, dbOpenDynaset)

'PREPARE EXCEL SPREADSHEET FOR IMPORT
objExcel.Application.Visible = False
objExcel.Parent.Windows(1).Visible = True
objExcel.Application.WORKSHEETS(1).Unprotect "PASSWORD"

intRow = 1

'DELETE EXISTING DATA
objExcel.Application.WORKSHEETS(1).Range("A2:M60000").Delete

intRow = 2

Do While rstTempF.EOF = False

With objExcel.Application.WORKSHEETS(1)
.Cells(intRow, 1).Value = rstTempF("FIELD1")
.Cells(intRow, 2).Value = rstTempF("FIELD2")
.Cells(intRow, 3).Value = rstTempF("FIELD3")
.Cells(intRow, 4).Value = rstTempF("FIELD4")
.Cells(intRow, 5).Value = rstTempF("FIELD5")
.Cells(intRow, 6).Value = rstTempF("FIELD6")
.Cells(intRow, 7).Value = rstTempF("FIELD7")
.Cells(intRow, 8).Value = rstTempF("FIELD8")

End With

intRow = intRow + 1

rstTempF.MoveNext

Loop

objExcel.Application.DISPLAYALERTS = False
‘NEW FILE NAME PASSED IN FROM ANOTHER FUNCTION
objExcel.SAVEAS strFileName

'SHOW SPREADSHEET
Set objExcel = GetObject(strFileName)
objExcel.Application.WORKSHEETS(1).Unprotect "Password"
objExcel.Application.Visible = True

Exit_MakeSAReport:

'SET WARNINGS BACK ON
DoCmd.SetWarnings True

rstTempF.Close
Set rstTempF = Nothing
Set objExcel = Nothing
Set dbTemp = Nothing

Exit Function

End Function ]

 

[abaldwin]

There is probably code in the close event of the excel spreadsheet that protects it. In other words you successfully unprotect the sheet and the user is fine. When they save or exti the sheet an excel macro re-protects it.

The solve is to look at the Excel sheet.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[GLENEE]

Within the VBAProject there is the normal Excel Objects i.e. Sheet1, Sheet2, Sheet3 and ThisWorkbook but there is no code and no macross. There is something called 'funcres(FUNCRES.XLA) which is password protected and seems to appear in all srpeadsheets, - not sure what that is?

 

[combo]

Changes made in excel file are stored when you save the file. You open strTemplate file, unprotect, and save as strFileName. As a result, strTemplate should stay protected, strFileName unprotected.
To check for workbook's auto macros, see ThisWorkbook module, if standard modules were added, see for older style Autpen/Auto_Close macros.
See (in excel) Tools>Addins. Analysis Toolpak (funcres.xla in VBE) and Solver are standard excel extensions. If there are other installed, uncheck them, however most likely they should not harm.
You could try to stop code after 'Exit_MakeSAReport:', can you edit worksheet?

combo

 

[GLENEE]

It's solved, thanks.
The line "You open strTemplate file, unprotect, and save as strFileName. As a result, strTemplate should stay protected, strFileName unprotected" by 'combo', did it for me.
I was re-protecting strTemplate after exporting the data (not shown in sample code above as a lot of other formatting etc was happening as well)and of course i didn't need to as i was saving as a different filename. It would appear that although i was unprotecting strFileName it would revert to its previous protected state as saved previous.