Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Work with Excel Spreadsheet from within Access

Posted on 2002-07-16
5
Medium Priority
?
419 Views
Last Modified: 2008-02-26
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
Comment
Question by:Tom Knowlton
  • 3
5 Comments
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 7158352
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
 
LVL 3

Accepted Solution

by:
pbryan earned 200 total points
ID: 7158664
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
 
LVL 12

Expert Comment

by:James Elliott
ID: 7159072
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
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 7159888
I ended up creating the macro.
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 7160691
   Dim objExcel As New Excel.Application

    objExcel.ActiveWorkbook.Save
   
    objExcel.Workbooks.Open strXLSName

    objExcel.Quit
   
     
    'DOES NOT CLOSE EXCEL!!!
    Set objExcel = Nothing
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Implementing simple internal controls in the Microsoft Access application.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question