Exporting Reports with subreports to excel (1 Viewer)

S

Snappy1263

Registered User.
Local time
Today, 15:50
Joined
Dec 8, 2015
Messages
130
Can I creat another database in 2010 n link the tables n import the queries.
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 15:50
Joined
Jun 21, 2011
Messages
5,899
Yeah, not going to work because we talking to an Excel 2010. So, you're going to need to make a copy of the database and convert and work out any issues that arise and I image there will be a few.
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 15:50
Joined
Jun 21, 2011
Messages
5,899
Oops, almost forgot... UNC Path to Template and Path to where you want it saved as well as naming convention.
 
S

Snappy1263

Registered User.
Local time
Today, 15:50
Joined
Dec 8, 2015
Messages
130
Ok I will make a new database. I will get back with u on the path I have to log into my work computer from home n get the path. Not sure what time zone ur in but its 9.25 Eastern time US...I will try to get that before I go to bed if not 1st thing in the morning...thank u
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 15:50
Joined
Jun 21, 2011
Messages
5,899
I'm in Eastern as well and have an appointment tomorrow so not hanging out on line all night :rolleyes: So, I will probably do this tomorrow night, no rush.
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 15:50
Joined
Jun 21, 2011
Messages
5,899
Hmm, you must have gotten busy... so this is just a reminder.
 
S

Snappy1263

Registered User.
Local time
Today, 15:50
Joined
Dec 8, 2015
Messages
130
So sorry my server was down so I couldnt log into my work computer. I let my IT guy know but I haven't heard back from him we don't work on Fridays we work four tens Monday through Thursday so when I want to do some work I have to login from home so as soon as I can get on I will get back to you thank you so very much
 
S

Snappy1263

Registered User.
Local time
Today, 15:50
Joined
Dec 8, 2015
Messages
130
Hey, final got into my server now that I am at work. Here is the path for the Template and saved one

Template:
G:\SUE'S STUFF\WORKING DESKTOP\2016 WESTLAND\WESTLAND EXPORT

SAVED:
N:\EXACT FIT\WESTLAND EXPORT PLUS

I also made a new database 2010. Let me know if you need anything else.

thank you so much
 
P

pujangga2007

New member
Local time
Tomorrow, 02:50
Joined
Dec 14, 2015
Messages
2
asking about export

in the attach file (picture), i'm success run macro ms access step 1,2, but is run 3 and code file 3.A is errorr, can correctly in macro even to export to excel (xlsx), many sheet and parameter created file []? example report_daily_[20151202]_[urgent].xls/data -> report_daily_20151202_urgent.xls tq
 

Attachments

  • tanya export.png
    tanya export.png
    35.5 KB · Views: 140
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 15:50
Joined
Jun 21, 2011
Messages
5,899
Will anyone else be using the Template? Because they may not have access to *Sue's Stuff*.

I will get to this later today... got a little busy today.
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 15:50
Joined
Jun 21, 2011
Messages
5,899
What are the Control names for these three fields? (The names of the Controls on the Form.)

ID
COVER PART NUMBER
DESCRIPTION

EDIT: What are you using as a Filter to send it out to Excel, the ID?
 
Last edited:
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 15:50
Joined
Jun 21, 2011
Messages
5,899
This part need to be pasted in the Forms Module. It is not finished because I need the additional information...

Code: 
 Function SendToExcel(strTQName As String, 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 send it to
     
         Dim rst As DAO.Recordset
         Dim ApXL As Object
         Dim xlWBk As Object
         Dim xlWSh As Object
         Dim fld As DAO.Field
         Dim lngMaxRow As Long
         Dim lngMaxCol As Long
         Dim strPath As String
     
         On Error GoTo Err_Handler
             ‘Location of Template
             strPath = “[COLOR=black][FONT=Segoe UI]G:\SUE'S STUFF\WORKING DESKTOP\2016 WESTLAND\WESTLAND EXPORT\Book1.xls[/FONT][/COLOR][FONT=Segoe UI]"[/FONT]
  
             Set rst = CurrentDb.OpenRecordset(strTQName)
             Set ApXL = CreateObject("Excel.Application")
         
         
             Set xlWBk = ApXL.Workbooks.Open(strPath)
             ApXL.Visible = True
                 
             ‘Set xlWSh = xlWBk.Worksheets(strSheetName)
             ‘xlWSh.Range("I1").Value = ????
             ‘xlWSh.Range("I2").Value = ????
             ‘xlWSh.Range("I3").Value = ????
  
             rst.MoveFirst
             xlWSh.Range("A8").CopyFromRecordset rst
             ' selects the first cell to unselect all cells
             xlWSh.Range("A8").Select
         
         xlWSh.Activate
         xlWSh.Cells.Rows(7).AutoFilter
         xlWSh.Cells.Rows(7).EntireColumn.AutoFit
     
         rst.Close
         Set rst = Nothing
         'Remove prompts to save the report
         ApXL.DisplayAlerts = False
         xlWBk.SaveAs "[COLOR=black][FONT=Verdana]N:\EXACT FIT\WESTLAND EXPORT PLUS\Westland_[/FONT][/COLOR]" Format(Date, "mm.dd.yyyy") & ".xlsx, 51
         ApXL.DisplayAlerts = True
         'ApXL.Quit
     
         Exit Function
 Err_Handler:
         DoCmd.SetWarnings True
         MsgBox Err.Description, vbExclamation, Err.Number
         Exit Function
  
 End Function
Then you need to put a Command Button on the Form and pasted this part (also unfinished) in the Event Procedure for the Command Button.

Code: 
         Dim dbs As DAO.Database
         Dim qryDef As DAO.QueryDef
         Dim strSQL As String
         Dim strWhere As String
         Dim lngLen As Long
         Set dbs = CurrentDb
     
     strSQL = "SELECT [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].ID, [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].[PART NUMBER], " & _
                 "[BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].[PART DESCRIPTION], [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].SUPPLIER, " & _
                 "[BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].COO, [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].[COST W FREIGHT], " & _
                 "[BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].UOM, [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].QUANITY, " & _
                 "[BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].Expr1 " & _
                     "FROM [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT]"
     'Number
     If Not IsNull(Me.txtID) Then
         strWhere = strWhere & "([ID] = " & Me.txtID & ") AND "
     End If
     
     lngLen = Len(strWhere) - 5
     
     If lngLen <= 0 Then
         strSQL = strSQL
         Set qryDef = dbs.CreateQueryDef("qryWestportExport", strSQL)
         'DoCmd.OpenQuery qryDef.Name
         qryDef.Close
         Set qryDef = Nothing
         DoEvents
         Call SendToExcel("qryWestportExport", "Sheet1")
         DoEvents
         DoCmd.DeleteObject acQuery, "qryWestportExport"
     Else
         strWhere = Left$(strWhere, lngLen)
     
         strSQL = strSQL & " WHERE " & strWhere
         Set qryDef = dbs.CreateQueryDef("qryWestportExport", strSQL)
         'DoCmd.OpenQuery qryDef.Name
         qryDef.Close
         Set qryDef = Nothing
         DoEvents
         Call SendToExcel("qryWestportExport", "Sheet1")
         DoEvents
         DoCmd.DeleteObject acQuery, "qryWestportExport"
     End If
     
         dbs.Close
         Set dbs = Nothing
 
Last edited:
S

Snappy1263

Registered User.
Local time
Today, 15:50
Joined
Dec 8, 2015
Messages
130
Hey thanks you have been working hard.

the control source names are the same name.

I didnt use a filter.


edit: but it should be the ID because that is how the Main form is linked to the sub forms right??
 
Last edited:
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 15:50
Joined
Jun 21, 2011
Messages
5,899
If that is the link then yes... If you were just making a report you would open by ID so we must do the same thing when exporting to Excel.

Will be working on this a bit later... my day job keeps getting in the way. :D
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 15:50
Joined
Jun 21, 2011
Messages
5,899
Updated code...

Code: 
 Function SendToExcel(strTQName As String, 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 send it to
     
         Dim rst As DAO.Recordset
         Dim ApXL As Object
         Dim xlWBk As Object
         Dim xlWSh As Object
         Dim fld As DAO.Field
         Dim lngMaxRow As Long
         Dim lngMaxCol As Long
         Dim strPath As String
     
         On Error GoTo Err_Handler
             ‘Location of Template
             strPath = “[COLOR=black][FONT=Segoe UI]G:\SUE'S STUFF\WORKING DESKTOP\2016 WESTLAND\WESTLAND EXPORT\Book1.xls[/FONT][/COLOR][FONT=Segoe UI]"[/FONT]
  
             Set rst = CurrentDb.OpenRecordset(strTQName)
             Set ApXL = CreateObject("Excel.Application")
         
         
             Set xlWBk = ApXL.Workbooks.Open(strPath)
             ApXL.Visible = True
                 
             ‘Set xlWSh = xlWBk.Worksheets(strSheetName)
             ‘xlWSh.Range("I1").Value = Me.ID
             ‘xlWSh.Range("I2").Value = Me.[[COLOR=black][FONT=Verdana]COVER PART NUMBER][/FONT][/COLOR]
             ‘xlWSh.Range("I3").Value = Me.[[COLOR=black][FONT=Verdana]DESCRIPTION][/FONT][/COLOR]
  
             rst.MoveFirst
             xlWSh.Range("A8").CopyFromRecordset rst
             ' selects the first cell to unselect all cells
             xlWSh.Range("A8").Select
         
         xlWSh.Activate
         xlWSh.Cells.Rows(7).AutoFilter
         xlWSh.Cells.Rows(7).EntireColumn.AutoFit
     
         rst.Close
         Set rst = Nothing
         'Remove prompts to save the report
         ApXL.DisplayAlerts = False
         xlWBk.SaveAs "[COLOR=black][FONT=Verdana]N:\EXACT FIT\WESTLAND EXPORT PLUS\Westland_[/FONT][/COLOR]" Format(Date, "mm.dd.yyyy") & ".xlsx, 51
         ApXL.DisplayAlerts = True
         'ApXL.Quit
     
         Exit Function
 Err_Handler:
         DoCmd.SetWarnings True
         MsgBox Err.Description, vbExclamation, Err.Number
         Exit Function
  
 End Function
For the Command Button
Code: 
         Dim dbs As DAO.Database
         Dim qryDef As DAO.QueryDef
         Dim strSQL As String
         Dim strWhere As String
         Dim lngLen As Long
         Set dbs = CurrentDb
     
     strSQL = "SELECT [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].ID, [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].[PART NUMBER], " & _
                 "[BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].[PART DESCRIPTION], [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].SUPPLIER, " & _
                 "[BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].COO, [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].[COST W FREIGHT], " & _
                 "[BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].UOM, [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].QUANITY, " & _
                 "[BOM PRICING EXTENDED DETAILS NON BOW S EXPORT].Expr1 " & _
                     "FROM [BOM PRICING EXTENDED DETAILS NON BOW S EXPORT]"
     'Number
     If Not IsNull(Me.ID) Then
         strWhere = strWhere & "([ID] = " & Me.ID & ") AND "
     End If
     
     lngLen = Len(strWhere) - 5
     
     If lngLen <= 0 Then
         strSQL = strSQL
         Set qryDef = dbs.CreateQueryDef("qryWestportExport", strSQL)
         'DoCmd.OpenQuery qryDef.Name
         qryDef.Close
         Set qryDef = Nothing
         DoEvents
         Call SendToExcel("qryWestportExport", "Sheet1")
         DoEvents
         DoCmd.DeleteObject acQuery, “qryWestportExport"
     Else
         strWhere = Left$(strWhere, lngLen)
     
         strSQL = strSQL & " WHERE " & strWhere
         Set qryDef = dbs.CreateQueryDef("qryWestportExport", strSQL)
         'DoCmd.OpenQuery qryDef.Name
         qryDef.Close
         Set qryDef = Nothing
         DoEvents
         Call SendToExcel("qryWestportExport", "Sheet1")
         DoEvents
         DoCmd.DeleteObject acQuery, "qryWestportExport"
     End If
     
         dbs.Close
         Set dbs = Nothing
Okay, so this is part one before I put in the second query. I need you to follow the instructions in Post #35 and then test. Report what happens.
 
S

Snappy1263

Registered User.
Local time
Today, 15:50
Joined
Dec 8, 2015
Messages
130
Thank u so much I will do it 1st thing in the morning. I appreciate this so much.
 
You must log in or register to reply here.

Similar threads

L
Need help with cross-tab report
Replies
6
Views
271
Pat Hartman
P
T
Export report in Desktop with account name
Replies
2
Views
125
Pat Hartman
P
K
Export to excel
Replies
3
Views
381
Gasman
Gasman
T
Report Creation from Microsoft Excel
Replies
8
Views
322
Isaac
Isaac
J
Solved Passing conditions to subreport with VBA.
Replies
10
Views
766
JoséBob
J

Users who are viewing this thread

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