Solved

Exporting from Access to Excel (Docmd.Transferspreadsheet) in a live window

Posted on 2004-03-24
9
572 Views
Last Modified: 2008-03-03
I have employed the code for the docmd.transferspreadsheet (thanks Nico).

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryMASTER_Temp", "C:\ListExport.xls"

However, instead of exporting and saving to the C:\ListExport.xls, I would like to export to a live window (I know how to open a live window in excel)

    Dim oApp As Object

    Set oApp = CreateObject("Excel.Application")
    oApp.Visible = True
    'Only XL 97 supports UserControl Property
    On Error Resume Next
    oApp.UserControl = True

Any suggestions on how to export in the window?

Thanks!
0
Comment
Question by:cassyerin
9 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 10669071
Greetings cassyerin!

  That command is in the category of a menu command and will not function in ways that say "CopyFromRecordset" would.

  With CopyFromRecordset you would set up the records in a dbdynaset type queue and pass them to an excel worksheet via CopyFromRecordset


regards
jack
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10669157
I use copyfromrecordset for all spreadsheet creations for end users because it shows the work as it occurs...
0
 

Author Comment

by:cassyerin
ID: 10671355
Do you know where I'm going wrong?

---


Dim objXLWb As Object
Dim objXLSheet As Object
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset(strSQL)

'Start Excel
Set objXLApp = CreateObject("Excel.Application")
objXLApp.Visible = True

'Open Worksheet & Activate
Set objXLWb = objXLApp.Workbooks.Add
Set objXLSheet = objXLWb.ActiveSheet

'CopyRecordset
objXLSheet.CopyFromRecordset rs
objXLSheet.Columns.AutoFit
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 3

Accepted Solution

by:
Jeremyw earned 125 total points
ID: 10671818
casseyerin,

this is how I am doing it.  change the filename in your DoCmd line to reference strFileName

Dim strFileName As String

strFileName = "C:\ListExport.xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryMASTER_Temp", strFileName

Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
xlApp.Visible = True
xlApp.Workbooks.Open strFileName

'Excel Formatting
    xlApp.Rows("1:1").Select
    xlApp.Selection.Insert Shift:=xlDown
    xlApp.Selection.Font.Bold = True
    With xlApp.Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With


Jeremy
0
 

Author Comment

by:cassyerin
ID: 10671836
I can get it to run using the docmd. option, however, I want it to export into excel so that data manipulation can be made and the user can choose where to save it.

0
 
LVL 32

Assisted Solution

by:jadedata
jadedata earned 125 total points
ID: 10671939
These changes do  not mean you did this wrong, just different


Dim objXLWb As Object
Dim objXLSheet As Object
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset(strSQL)
'where is the strsql value
'did you actually write this to do a spreadsheet on ANY recordset???

'Start Excel
Set objXLApp = New Excel.Application
objXLApp.Visible = True

'Open Worksheet & Activate
Set objXLWb = objXLApp.Workbooks.Add
Set objXLSheet = objXLWb.worksheets(1)
'delete other 2 worksheets  
objXLWb.worksheets(3).delete
objXLWb.worksheets(2).delete

Add fields column headers
for x = 0 to rs.fields.count-1
  objxlws.cells(HeaderRowNum, x+1) = rs.fields(x).name
next x

'CopyRecordset
objXLSheet.range("A4").CopyFromRecordset rs
objXLSheet.Range("A:Z").entirecolumn.AutoFit
0
 
LVL 2

Expert Comment

by:rsclark
ID: 10676033
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12481235
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    Split: jadedata {http:#10671939}, Jeremyw {http:#10671818}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

stevbe
EE Cleanup Volunteer
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …

831 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