unprotect excel on access export

[baronqueefington]

Hello,

I use the code below to export a table from access to excel. I now want unprotect the spreadsheet on open and protect it on close.

I have looked at the 'excelsheet.Unprotect Password:= "password in quotes" ', but I have also read that it doesn't work with objects?

Private Sub cmdExport1_Click()
Dim strTQName As String
Dim strSheetName As String
Dim strFilePath As String

' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to
' strFilePath is the name and path of the file you want to send this data into.

strTQName = "tblDolsData"
strFilePath = "C:\Users\Paul\Desktop\backup"
strSheetName = "Dols Data"

Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As Field


Set rst = CurrentDb.OpenRecordset(strTQName)
Set ApXL = CreateObject("Excel.Application")

Set xlWBk = ApXL.Workbooks.Open(strFilePath)


ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets(strSheetName)

xlWSh.Range("a2").Select


For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
rst.MoveFirst
xlWSh.Range("a2").CopyFromRecordset rst
xlWSh.Range("1:1").Select



End Sub

Any help would be great.

Thank you.

[June7]

If you read it doesn't work with objects then I guess that is the case. Did you prove by attempting? This thread (see post 11) seems to indicate it is possible http://www.office-forums.com/threads...failed.493345/

[baronqueefington]

Thank you again! It worked!

Here is the revised code for anyone in the future looking to import a specific table from access to a specific protected work sheet in excel.

Private Sub cmdExport2_Click()

Dim strTQName As String
Dim strSheetName As String
Dim strFilePath As String
Dim strPassword As String
Dim binReadOnly As Boolean

Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As Field
strPassword = "bob"

strTQName = "tblDolsData"
strFilePath = "C:\Users\Paul\Desktop\backup"
strSheetName = "Dols Data"

' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to
' strFilePath is the name and path of the file you want to send this data into.



Set rst = CurrentDb.OpenRecordset(strTQName)
Set ApXL = CreateObject("Excel.Application")

Set xlWBk = ApXL.Workbooks.Open(strFilePath, , binReadOnly, , strPassword)
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets(strSheetName)

xlWSh.Range("a1").Select


For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
rst.MoveFirst
xlWSh.Range("a2").CopyFromRecordset rst
xlWSh.Range("1:1").Select



End Sub

[baronqueefington]

One more question, I had to add the binReadOnly parts for it to work correctly, it gives a class error if they are not there. Would you have an idea why? Just for my own knowledge.

Thanks again.

[June7]

I have no idea what binReadOnly is about.

[baronqueefington]

Apologies, but one more thing. I am looking for the code to set the focus to "dols data" work sheet on the excel workbook load.

I need this due to the fact that if the workbook is saved and closed whilst not focused on the sheet in which the code send the records to, then the next time I run the code it shows an error.

I suppose I could set the excel workbook to set focus to the correct sheet on load instead?

[June7]

Try:

xlWBk.Worksheets("dols data").Activate

[baronqueefington]

Thank you that worked perfectly!