• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

Work with Excel Spreadsheet from within Access

I know how to export a query out to Excel in VBA.

After the spreadsheet is created, I want to programmatically do the following:

1)  Create a "Split" (panes) in the spreadsheet.  Coordinates would be at cell A4.

2)  Freeze the panes.

This would be the equivalent of choosing Window |Split in Excel, positioning the split to cell A4, then choosing Window | Freeze Panes.
0
Tom Knowlton
Asked:
Tom Knowlton
  • 3
1 Solution
 
Tom KnowltonWeb developerAuthor Commented:
Even better, a few good web sites that have tutorials on this kind of thing, and the layout of the Excel object model.  Then I could help myself in the future.

Thanks!
0
 
pbryanCommented:
what I find easiest is to create a macro in excel, and then use that code in access...

In access you have to add a reference to the Excel application or in code use

set myexcel=createobject("Excel.application")

then all you usually have to do is add myexcel. infront of all you lines from the excel macro

----
have a look at http://www.mvps.org/dmcritchie/excel/excel.htm
0
 
James ElliottManaging DirectorCommented:
Something like this: (Make sure your excel library is referenced in your VB editor)

Dim xlapp As Object

Set xlapp = CreateObject("Excel.Application")

Workbooks("c:\BOOK1.XLS").Worksheets("Sheet1").Activate
With ActiveWindow
    .SplitColumn = 1
    .SplitRow = 2
    .Freezepanes = true
End With

Thanks

Jell

0
 
Tom KnowltonWeb developerAuthor Commented:
I ended up creating the macro.
0
 
Tom KnowltonWeb developerAuthor Commented:
   Dim objExcel As New Excel.Application

    objExcel.ActiveWorkbook.Save
   
    objExcel.Workbooks.Open strXLSName

    objExcel.Quit
   
     
    'DOES NOT CLOSE EXCEL!!!
    Set objExcel = Nothing
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now