Exporting Reports with subreports to excel (1 Viewer)

Yes, because a crosstab displays the data across whereas a Select query displays it down.

OK I WAS PLAYING AROUND WITH IT AND I AM A LITTLE CONFUSED LOL.... I WATCHED A YOU TUBE VIDEO TO TRY AND FIGURE IT OUT BUT WAS UNSURE...

CAN YOU GUIDE ME

MY HEAD HURTS.... lol

Well I got it to put in the labor time but i think i can re-work the template to list the Cut Sew etc... let me play with it and I will get back to you.... I think I am getting good at this.... but I am sure you have a way to do codes but this may be better and less work...

You ROCK BTW

Ok it works that way but is there a way to have 3 footers instead of 1...

Reasons i have 3 part numbers (3 lines that export to the template) each line has these labors....it works to export 1 line but can it do more....

If not i would have to have 3 separate uploads im sure this will have more codes to write but just wanted to ask...i have attached the excel sheet with all the fields

The hilighted blue is the code that runs can it do the sam for the other 2 lines or will it be too many codes.

let me know what you think. Thanks so much

Nope, no other way without a crosstab that show all the information in one query.

OK so I will have to do 3 separate exports to get the data in the places i need. Which is ok but this is getting so complicated that i am the only one that will be able to do this. our new system is not very good for my job but i didn't have a say lol.....

Thank you for all your help. I think I have what I need. But I will keep in touch, I will have another project coming up soon that I might need your help on. Thank you for teaching me all this new stuff.

What is it they say? *No pain, no gain!* Never knew if that was good or bad

You're welcome and I'll be around.

Hi its me again.

everything is working great... except 2 things.

1). if there is not data in the subform but there is info on the main form that needs to export. It say not record so so it wont export. is there a way to tell it to export anyway.

Here is the current code.

Private Sub Command579_Click()

Dim outReportData As String
Dim xlFileName As String


Dim dbs As DAO.Database
Dim qryDef As DAO.QueryDef
Dim qryDefFooter As DAO.QueryDef
Dim strSQL As String
Dim strSQLFooter
Dim strWhere As String
Dim lngLen As Long
Set dbs = CurrentDb

strSQL = "SELECT[PART NUMBER], Expr1 " & _
"FROM quniExportToExcelBK"

strSQLFooter = "SELECT[PART NUMBER], Expr6 " & _
"FROM [Query2]"

'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
Call SendToExcel("qryWestportExport", "Sheet1")
DoCmd.DeleteObject acQuery, "qryWestportExport"
DoEvents
strSQLFooter = strSQLFooter
Set qryDefFooter = dbs.CreateQueryDef("qryWestportExportFooter", strSQLFooter)
'DoCmd.OpenQuery qryDef.Name
qryDefFooter.Close
Set qryDefFooter = Nothing
Call SendToExcelFooter("qryWestportExportFooter", "Sheet1")
DoCmd.DeleteObject acQuery, "qryWestportExportFooter"
Else
strWhere = Left$(strWhere, lngLen)
strSQL = strSQL & " WHERE " & strWhere
Set qryDef = dbs.CreateQueryDef("qryWestportExport", strSQL)
'DoCmd.OpenQuery qryDef.Name
qryDef.Close
Set qryDef = Nothing
Call SendToExcel("qryWestportExport", "Sheet1")
DoCmd.DeleteObject acQuery, "qryWestportExport"
DoEvents
strSQLFooter = strSQLFooter & " WHERE " & strWhere
Set qryDefFooter = dbs.CreateQueryDef("qryWestportExportFooter", strSQLFooter)
'DoCmd.OpenQuery qryDef.Name
qryDefFooter.Close
Set qryDefFooter = Nothing
Call SendToExcelFooter("qryWestportExportFooter", "Sheet1")
DoCmd.DeleteObject acQuery, "qryWestportExportFooter"
End If

dbs.Close
Set dbs = Nothing



End Sub



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 = "S:\Allfiles\GLBT\BOM EXPORT\Book2.xls"

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("A2").Value = Me.[SEWING PART NUMBER]

rst.MoveFirst
xlWSh.Range("B2").CopyFromRecordset rst

rst.Close
Set rst = Nothing
'Remove prompts to save the report
ApXL.DisplayAlerts = False
xlWBk.SaveAs "S:\Allfiles\GLBT\BOM EXPORT\BAYLINER\BOMS\BLBOW" & 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
Function SendToExcelFooter(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 Workbook
strPath = "S:\Allfiles\GLBT\BOM EXPORT\BAYLINER\BOMS\BLBOW" & Format(Date, "mm.dd.yyyy") & ".xlsx"

Set rst = CurrentDb.OpenRecordset(strTQName)
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)
Set xlWSh = xlWBk.Worksheets(strSheetName)

ApXL.Visible = True
rst.MoveFirst
xlWSh.Range("A46").CopyFromRecordset rst
'selects the first cell to unselect all cells
xlWSh.Range("B2").SELECT

xlWSh.Activate
xlWSh.Cells.Rows(1).AutoFilter
xlWSh.Cells.Rows(1).EntireColumn.AutoFit

rst.Close
Set rst = Nothing
'Remove prompts to save the report
ApXL.DisplayAlerts = False
xlWBk.Save
ApXL.DisplayAlerts = True
'ApXL.Quit

Exit Function
Err_Handler:
DoCmd.SetWarnings True
MsgBox Err.DESCRIPTION, vbExclamation, Err.Number
Exit Function

End Function

2). we talked about his briefly but is there a way to do all records at once instead of one record at a time.

thanks again for your help.

got another question

you have the template save as by the date. is there any way that it can be saved as a sequential number each time. we are doing many of these on the same day and we have to constantly saves as. if it can do it for us this will save us some time. can we add at number starting a 1 on the end???

thanks,

ok i figured out how to rename with time and date but i was thinking can you save as with a name that is in the cell like A2. so the file is saved as the part number in the excel sheet??? my mind is on a roll LOL...

thanks,

Hey there....havent heard from you but i am sure you are busy...just wanted to make you were notified that i sent you some stuff.

thanks,

Ok so i have been trying to figure this out for a couple days and then i realized the problem.....when the export does the save as it puts the date and then runs the second code to put the footer in. obviously the names have to be the same so it will look for it to put the footer there. I am trying to save time and avoid to doing a save as for hundreds of records. So i am hoping that there can be a way to save as the date like it is and then a field on the form so each time you hit the button to export the file name will be different each time so there is no need to keep doing a save as so it doesnt override each time. since i have to do each record at a time...

i am in a crunch right now and all this is making me CRAZY.....LOL....i have researched this and found several answers but putting it in your code is something beyond me.

thanks,

Yep, I am busy... working on three Projects at once...

If I understand you correctly you want to save it with a different name each time so you won't overwrite the previous file? What happens to the all the other saves? If you are just wanting to save then I don't see why it's an issue to overwrite.

Thank u for getting back to me. Sorry to have bothered u, I tried to figure it out myself but couldnt find anything that worked.

Yes you are correct. There are 2 reasons why...one is so that it will automatically save we are doing hundreds of records n adding a letter on to save it as a different name it is taking alot if time n can cause errors...secondly to help with identifying the file n eliminate errors I would like the file name to be the part number on the form of the record that is exporting this way we can identify the file easily.

Thank u so much

Hey there,

Its been a long time. Hope you are well. I have run into a small problem and I know you will be able to help. I searched as much as I can to find the answer.

As you know I have a template that my data exports to using all the codes you helped me with. Well I have to do drop down list in this template. That worked fine but as I was doing this I realized that it would work better if the drop down list match up other information in the next cells. So I used Vlookup.

Example:
The data that is exported are part numbers in one cell, then the description in the next cell. The part number cell I have a drop down so it can be changed to a certain color material but the description doesn't follow in the next cell So I figured to put a vlookup in the description cell but the data overrides the formula in that cell when it gets exported. Is there a way so it doesn't override the cell.

I have tried protecting the cell, I have tried using blank info that is exported but it still overrides it. Also when the part numbers comes over to the template they are a text. This will also cause a vlookup not to work, can that be fixed.

I have attached the excel sheet so you can see.

thank you so very much.

Hi there, it has been quite a while. I hope are doing well. I have something probably pretty simple for you. I have tried figuring this out from everything you have taught me but I am stumped. I have a report that I would like to export into an excel template. Can you give me an example of the code. Does the data come from the query (I assume so). My report has formulas in but he query does not. Will I have to do that in the query for it to pull the data. Thank you so much. I know its something simple.

Hmm, can't even think about this till tomorrow, getting ready for a conference call.

Thats fine. I know how busy you are. I will keep trying too. Thanks

This is a code I use to send the report to excel. I just want it to go to a template

Private Sub Command8_Click()



Dim outReportData As String
Dim xlFileName As String


outReportData = "SHARKSKIN EXPORT PRICING"


xlFileName = "S:\Allfiles\SUE'S STUFF\WORKING DESKTOP\2021 WESTLAND\ACCESSORIES\2021\ACCESSORIES.XLS"

DoCmd.OutputTo acOutputReport, outReportData, acFormatXLS, xlFileName

End Sub

I think I did it
Private Sub Command8_Click()

Dim filepath As String
Dim sSheetName As String
filepath = "S:\Allfiles\SUE'S STUFF\WORKING DESKTOP\2021 WESTLAND\ACCESSORIES\2021\2021 ACCESSORES .COM PRICING.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "BOM EXPORT", filepath, True, sSheetName

MsgBox ("The Excel has been Successfully exported")
End Sub

My template is on a different tab, and I am going to use the exported info and link it to the tab with the template.