OutPut Issues....Access 2007 (1 Viewer)

H

HaroldIII

Registered User.
Local time
Today, 12:06
Joined
Feb 2, 2011
Messages
55
DoCmd.OutputTo acOutputQuery, "Knight Compare", acFormatXLSX, "c:\apps\xp\Desktop\Project File\EXCEL FILES\Output Folder\Knight \Knight.xlsx", False

Looks like ur basic output code; However, The issue im having is the actual output size is over 205000 rows but when I run this module, Only 65000 rows are saved to the XLS file.

Is there a way to have the module save all the rows??

PLEASE HELP...:confused:
 
H

HaroldIII

Registered User.
Local time
Today, 12:06
Joined
Feb 2, 2011
Messages
55
boblarson said:
Don't use OutputTo as I think it is still limited.

Use the code on my website:

But you'll have to modify it to save the file. I can help with that later but I have meetings I'm about to go into and won't be available for a few hours.
Click to expand...


I guess i should have also added that everything i know about Access I tought myself thru the help menu. looking at ur code...i almost fell out of my chair...haha...

although i must say i am quite intregued....maybe if you can give me some clarity otherwise...ill be researching what that all means...lol

thanks
 
boblarson

boblarson

Smeghead
Local time
Today, 09:06
Joined
Jan 12, 2001
Messages
32,059
HaroldIII said:
I guess i should have also added that everything i know about Access I tought myself thru the help menu. looking at ur code...i almost fell out of my chair...haha...

although i must say i am quite intregued....maybe if you can give me some clarity otherwise...ill be researching what that all means...lol

thanks
Click to expand...

Okay, instructions -

1. Create a new standard module by clicking on CREATE tab and then selecting MODULE from the far right on the Ribbon.

2. Paste the code I give you below and do NOT change anything EXCEPT perhaps the formatting code I included as an example of how to do some formatting. Leave the rest alone, do not put your query into it or anything else. It is generic and we'll call it from where you are currently using the DoCmd.OutputTo code.

3. Here's the code to paste in the module and save the module with the name basExcelFunctions.

Code: 
Option Compare Database
Option Explicit
Public Function SendTQ2Excel(strTQName As String, strFilePathAndName As String, Optional strSheetName 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 name it to
    
    Dim rst As DAO.Recordset
    Dim ApXL As excel.Application
    Dim xlWBk As excel.Workbook
    Dim xlWSh As excel.Worksheet
    Dim fld As DAO.Field
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
    On Error GoTo err_handler
    Set rst = CurrentDb.OpenRecordset(strTQName)
    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Add
    ApXL.Visible = True
        
    Set xlWSh = xlWBk.Worksheets("Sheet1")
    If Len(strSheetName) > 0 Then
        xlWSh.Name = Left(strSheetName, 34)
    End If
    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
    ' This is included to show some of what you can do about formatting.  You can comment out or delete
    ' any of this that you don't want to use in your own export.
    With ApXL.Selection.Font
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
    End With
    ApXL.Selection.Font.Bold = True
    With ApXL.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select
    ' does the "autofit" for all columns
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
    ' selects the first cell to unselect all cells
    xlWSh.Range("A1").Select
    
    'Saves the workbook
    xlWBk.SaveAs strFilePathAndName
    ' closes the workbook
    xlWBk.Close
    ' quits Excel and removes variable
    ApXL.Quit
    Set ApXL = Nothing
    
    rst.Close
    Set rst = Nothing
    Exit Function
err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    Exit Function
End Function

Then, where you currently call the DoCmd.OutputTo code, put this instead:

Code: 
Call SendTQ2Excel("Knight Compare", "c:\apps\xp\Desktop\ProjectFile\EXCEL FILES\Output Folder\Knight \Knight.xlsx", acFormatXLSX)
 
boblarson

boblarson

Smeghead
Local time
Today, 09:06
Joined
Jan 12, 2001
Messages
32,059
Sorry, I forgot to mention that you either need to change this part:
Code: 
    Dim ApXL As excel.[URL="http://www.access-programmers.co.uk/forums/showthread.php?p=1042224#"][COLOR=darkgreen]Application[/COLOR][/URL]
    Dim xlWBk As excel.Workbook
    Dim xlWSh As excel.Worksheet

to this:
Code: 
    Dim ApXL As [B][COLOR=red]Object[/COLOR][/B]
    Dim xlWBk As [B][COLOR=#ff0000]Object[/COLOR][/B]
    Dim xlWSh As [B][COLOR=#ff0000]Object[/COLOR][/B]

or you have to set a reference to Microsoft Excel 12.0 Object Library (or 14.0 if on Access 2010)
 
H

HaroldIII

Registered User.
Local time
Today, 12:06
Joined
Feb 2, 2011
Messages
55
Im getting an compile error...ambiguous name detected: SendTQ2Excel

Here is what I actually have...First what you gave me saved as Basexcelfunctions

Option Compare Database
Option Explicit
Public Function SendTQ2Excel(strTQName As String, strFilePathAndName As String, Optional strSheetName 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 name it to

Dim rst As DAO.Recordset
Dim ApXL As excel.Application
Dim xlWBk As excel.Workbook
Dim xlWSh As excel.Worksheet
Dim fld As DAO.Field
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
On Error GoTo err_handler
Set rst = CurrentDb.OpenRecordset(strTQName)
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(strSheetName) > 0 Then
xlWSh.Name = Left(strSheetName, 34)
End If
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
' This is included to show some of what you can do about formatting. You can comment out or delete
' any of this that you don't want to use in your own export.
With ApXL.Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select

'Saves the workbook
xlWBk.SaveAs strFilePathAndName
' closes the workbook
xlWBk.Close
' quits Excel and removes variable
ApXL.Quit
Set ApXL = Nothing

rst.Close
Set rst = Nothing
Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function
End Function


Then I pasted the final line of code where i originally had my DoCmd.OutputTo code:

Public Function ARCA_Compare_Output()
DoCmd.OutputTo acOutputQuery, "Citadel Compare", acFormatXLSX, "H:\apps\xp\Desktop\ARCA Project File\EXCEL FILES\Output Folder\Citadel\Citadel.xlsx", False

DoCmd.OutputTo acOutputQuery, "Englander Compare", acFormatXLSX, "H:\apps\xp\Desktop\ARCA Project File\EXCEL FILES\Output Folder\Englander\Englander.xlsx", False

Call SendTQ2Excel("Knight Equity Compare", acFormatXLSX, "H:\apps\xp\Desktop\ARCA Project File\EXCEL FILES\Output Folder\Knight Equity\KnightEquity.xlsx", False)

DoCmd.OutputTo acOutputQuery, "OTA Compare", acFormatXLSX, "H:\apps\xp\Desktop\ARCA Project File\EXCEL FILES\Output Folder\OTA\OTA.xlsx", False

DoCmd.OutputTo acOutputQuery, "Susq Compare", acFormatXLSX, "H:\apps\xp\Desktop\ARCA Project File\EXCEL FILES\Output Folder\SUSQ\SUSQ.xlsx", False

DoCmd.OutputTo acOutputQuery, "VTrader Compare", acFormatXLSX, "H:\apps\xp\Desktop\ARCA Project File\EXCEL FILES\Output Folder\VTrader\Vtrader.xlsx", False

End Function
___________________

Should i be running the modules in a certain order??
 
boblarson

boblarson

Smeghead
Local time
Today, 09:06
Joined
Jan 12, 2001
Messages
32,059
The ambiguous name detected error means that somewhere you already have a function named SendTQ2Excel.
 
H

HaroldIII

Registered User.
Local time
Today, 12:06
Joined
Feb 2, 2011
Messages
55
Yes...I had a test module with that code in it...I deleted it...

Now i have another compile error
Wrong number of arguments or invalid property assignment
 
boblarson

boblarson

Smeghead
Local time
Today, 09:06
Joined
Jan 12, 2001
Messages
32,059
HaroldIII said:
Yes...I had a test module with that code in it...I deleted it...

Now i have another compile error
Wrong number of arguments or invalid property assignment
Click to expand...

You have this messed up:
Code: 
[B]Call SendTQ2Excel("Knight Equity Compare", acFormatXLSX, "H:\apps\xp\Desktop\ARCA Project File\EXCEL FILES\Output Folder\Knight Equity\KnightEquity.xlsx", False)[/B]

It needs to be this:
Code: 
[B]Call SendTQ2Excel("Knight Equity Compare", "H:\apps\xp\Desktop\ARCA Project File\EXCEL FILES\Output Folder\Knight Equity\KnightEquity.xlsx")[/B]
 
H

HaroldIII

Registered User.
Local time
Today, 12:06
Joined
Feb 2, 2011
Messages
55
Yep cought that...I changed it and it worked....

Thanks for ur patience and your help!!
 
You must log in or register to reply here.

Similar threads

PaquettePaul
Solved Output to Excel
Replies
14
Views
1,170
Gasman
Gasman
Z
Assistance - Issue with Expression Function in SQL Query and Invoking Function in VBA Editor
Replies
4
Views
498
June7
June7
W
CSV or XLSX Bank Statement to Access module (need to import via VBA and then write back to Excel with imported flag)
Replies
14
Views
1,156
Cotswold
Cotswold
monheimx9
Generating QR Code for Access using the qr-rs library/CLI written in Rust
Replies
5
Views
1,154
DickyP
DickyP
ironfelix717
KMLFile object - Easily manipulate KML Files
Replies
0
Views
853
ironfelix717
ironfelix717

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom