Freeze pane in Excel from Access database

[Malcy]

Hi
I have searched forum and found some code to export an Excel workbook and then get into one of the worksheets and format it.
What I want to do is export a crosstab query as a workbook (which works OK) but then freeze the pane on cell B2 so that the column and row heading stay visible when it is used on a PocketPC.
Does anyone know the code I should use for this?
Any help greatly appreciated.
Best wishes

Malcy

 

[spacepro]

Try something like:

Sheets("Sheet1").select
sheet1.range("B2").activate
activewindow.freezepanes = true

Change Sheet1 to your worksheet name.

Hope this helps

Andy

 

[Malcy]

Thanks Andy
I will try that one out this evening.
It will be neat if it works
Best wishes

Malcy

 

[Malcy]

I tried this out this afternoon - first chance!!!
Didn't work at first but realised I had to check Excel in the references.
The code I have is
_____________________
Dim cnn As ADODB.Connection
Dim xlApp As Excel.Application
Dim clSheet As Excel.Worksheet

' Delete the old copies of the export files so that the new files only have one worksheet each
Kill "C:\MgmShp\Export\MgmShpExp.xls"
Kill "C:\MgmShp\Export\MgmShpWeekFix.xls"

' Export the data to Excel files in the C:\ directory
DoCmd.TransferSpreadsheet acExport, 8, "qxtbExport", "C:\MgmShp\Export\MgmShpExp.xls", True, ""
DoCmd.TransferSpreadsheet acExport, 8, "tblWeekFix", "C:\MgmShp\Export\MgmShpWeekFix.xls", True, ""

' Manipulate the spreadsheet to fix pane at cell B2
Set xlApp = CreateObject("Excel.Application")
xlApp.Application.Visible = False
xlApp.Workbooks.Open "C:\MgmShp\Export\MgmShpExp.xls"
Sheets("qxtbExport").select
qxtbExport.range("B2").Activate
ActiveWindow.FreezePanes = True
ActiveWorkbook.Save
xlApp.Application.Visible = True
Set xlSheet = Nothing
Set xlApp = Nothing

' Update the download check in tblWeekControl
Set cnn = CurrentProject.Connection
cnn.Execute "UPDATE [tblWeekControl] INNER JOIN [tblWeekFix] ON ([tblWeekControl].[lngWcYear] = [tblWeekFix].[lngYearFix]) AND ([tblWeekControl].[bytWcWeek] = [tblWeekFix].[bytWeekFix]) SET [tblWeekControl].[bExport] = Yes;"
Set cnn = Nothing
MsgBox "The tables have been successfully exported", vbOKOnly, "Progress information"
____________________

I get an error message saying object required, but it seems to have locked me out of the spreadsheet. When I opened it read-only it hasn't got as far as freezing the pane.
I tried commenting out the ActiveWorkbook.Save line, since I wasn't sure that was meant to be there, and re-running it but it denied me permission!
Any ideas what I have got wrong??
Thanks for any clues

Malcy

 

[spacepro]

When you get the error message, and press debug which line is highlighted?

Have you tried setting breakpoints on the code and stepping through it and also set the xlapp.application.visible to true for testing purposes?

Andy

 

[Malcy]

Thanks Andy
It didn't give me a debug option. Now trying to run it again to see if I missed something and it still says permission denied. Went to Windows Explorer to delete the spreadsheet but it wont let me saying I need to close it from Excel but I cannot open it in Excel so seems to be tryly jiggered. Will try rebooting. That cleared it.
Tried commenting out lines since not sure how to do breakpoints and step into.
The "object required" error seemsto come on the line

qxtbExport.range("B2").Activate

I wondered whether somewhere I should have set the xlSheet since it is dimensioned at the start. Trouble is I haven't done all that much with VBA and nothing with Excel VBA so not sure what may be wrong.
Thanks for your time and effort!
Best wishes

Malcy

 

[spacepro]

Sounds like excel is still open after the code has completed. I am not too hot with excel code from access myself.

To set a breakpoint, go into the vba editor and in the column to the left of the code using your mouse click in the column where the code is and a red spot will appear. Exit VBA editor and run the code, when the code reaches this point the vba editor will appear and it will be highlighted in yellow and you can place your cursor over the variables and it will give you the values.

You can then step through the code, it does give you a better understanding of the code.

I have never using automation in access, I have always done it in excel directly. Any chance of posting an example of the spreadsheet/db?

I am guessing here but have you tried putting the following code after the save code

activeworkbook.close

Andy

 

[Malcy]

Thanks Andy
Did toggle and step into and the problem is clearly with the line qxtbExport.range
I also added your close line since that made a lot of sense.
It seems that it is not recognising the worksheet as an object, and then stops but leaves everything open which is why I was having difficulties earlier.
I have attached the relevant bits but dropped out a lot of the data from the backend to make it smaller. I think you will be able to get the jist of it. The programming may not be brilliant so please don't laught too hard!
Thanks again

Malcy

 

[spacepro]

Hi Malcy,

Replace your excel code with this :

    Dim xlapp As Object
    Dim xlwkb As Object
    
    Set xlapp = CreateObject("Excel.Application")
    xlapp.Application.Visible = False
    
    xlapp.workbooks.Open "c:\mgmshp\export\mgmshpexp.xls"
    
    Set xlwkb = xlapp.Application.activeworkbook
    
    With xlwkb
        .worksheets(1).cells(2, 2).Activate
    End With
        
    xlapp.activewindow.freezepanes = True
    MsgBox "Freeze Panes - Success", vbInformation, "Success"
        
        
    xlapp.Application.Visible = True
    Set xlapp = Nothing
    Set xlwkb = Nothing

This does the job. Let me know if it works for you!

Andy

 

[Malcy]

Thanks Andy
That worked a treat! Very much appreciate your help. I can follow what it is doing but could not have managed it myself. Makes me wonder about doing a bit more with the spreadsheet now I have found the rudiments.
Have a good Easter
Best wishes

Malcy

 

[spacepro]

Thanks Malcy, you have a good easter as well.

Andy