Export Query To Excel With Multiple Criteria (2 Viewers)

Sevn

Sevn

I trust ME!
Local time
Today, 04:27
Joined
Mar 13, 2008
Messages
97
I don't understand what you mean by "break point". Can you explain further?
 
Sevn

Sevn

I trust ME!
Local time
Today, 04:27
Joined
Mar 13, 2008
Messages
97
Hello All,

I am very sorry for being difficult, but I have no idea how to see the values of my variables the way you are describing to me. I do see that the strSQL variable is trying to be set before the strVolCat value is set, could this be the problem?

Maybe my code should be as follows:

Code: 
Option Compare Database
Private Sub cmdExport_Click()

Dim xlapp As Excel.Application
Dim wb As Excel.workbook
Dim ws As Excel.worksheet
Dim rs As Recordset
Dim strSQL As String
Dim strVolCat As String

Select Case [Forms]![Frm-SelectCompanyVolCat]![Combo94]

Case "1A"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1A")
    strVolCat = "1A"[COLOR="Red"]

strSQL = "SELECT DefineCPMVolCatByCustomer.ParentTieID, DefineCPMVolCatByCustomer.Customer, DefineCPMVolCatByCustomer.Volume, DefineCPMVolCatByCustomer.GrossPerTon, DefineCPMVolCatByCustomer.GrossSales, DefineCPMVolCatByCustomer.GTNPerTon,  DefineCPMVolCatByCustomer.GTNSales, DefineCPMVolCatByCustomer.PricePerTon, DefineCPMVolCatByCustomer.NetSales, DefineCPMVolCatByCustomer.COGSPerTon, DefineCPMVolCatByCustomer.COGS, DefineCPMVolCatByCustomer.MarginSales, DefineCPMVolCatByCustomer.MarginPerTon, DefineCPMVolCatByCustomer.MarginPct FROM DefineCPMVolCatByCustomer Where VolCat = '" & strVolCat & "'"[/COLOR]

    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show

End Select

Set rs = Nothing
Set ws = Nothing
Set wb = Nothing
Set xlapp = Nothing

End Sub
 
M

MRdNk

Registered User.
Local time
Today, 10:27
Joined
Sep 27, 2008
Messages
16
Sevn said:
Hello All,

I am very sorry for being difficult, but I have no idea how to see the values of my variables the way you are describing to me. I do see that the strSQL variable is trying to be set before the strVolCat value is set, could this be the problem?

Maybe my code should be as follows:

Code: 
Option Compare Database
Private Sub cmdExport_Click()

Dim xlapp As Excel.Application
Dim wb As Excel.workbook
Dim ws As Excel.worksheet
Dim rs As Recordset
Dim strSQL As String
Dim strVolCat As String

Select Case [Forms]![Frm-SelectCompanyVolCat]![Combo94]

Case "1A"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1A")
    strVolCat = "1A"[COLOR="Red"]

strSQL = "SELECT DefineCPMVolCatByCustomer.ParentTieID, DefineCPMVolCatByCustomer.Customer, DefineCPMVolCatByCustomer.Volume, DefineCPMVolCatByCustomer.GrossPerTon, DefineCPMVolCatByCustomer.GrossSales, DefineCPMVolCatByCustomer.GTNPerTon,  DefineCPMVolCatByCustomer.GTNSales, DefineCPMVolCatByCustomer.PricePerTon, DefineCPMVolCatByCustomer.NetSales, DefineCPMVolCatByCustomer.COGSPerTon, DefineCPMVolCatByCustomer.COGS, DefineCPMVolCatByCustomer.MarginSales, DefineCPMVolCatByCustomer.MarginPerTon, DefineCPMVolCatByCustomer.MarginPct FROM DefineCPMVolCatByCustomer Where VolCat = '" & strVolCat & "'"[/COLOR]

    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show

End Select

Set rs = Nothing
Set ws = Nothing
Set wb = Nothing
Set xlapp = Nothing

End Sub
Click to expand...

Yeah - that would be an issue. Does it work now?
Did you figure out how to add a breakpoint? If you have, once you have the breakpoint on you can hover-over a variable ie. strSQL and it'll tell you the value of that variable.
 
M

MRdNk

Registered User.
Local time
Today, 10:27
Joined
Sep 27, 2008
Messages
16
I did a quick screen shot to demonstrate, hope this helps.
 

Attachments

  • breakpoint.jpg
    breakpoint.jpg
    97.2 KB · Views: 152
Sevn

Sevn

I trust ME!
Local time
Today, 04:27
Joined
Mar 13, 2008
Messages
97
Hello,

I have attempted to move the strSQL query in the following way, and got the same error, but now it says Expected 2.

Code: 
[SIZE="2"]Option Compare Database
Private Sub cmdExport_Click()

Dim xlapp As Excel.Application
Dim wb As Excel.workbook
Dim ws As Excel.worksheet
Dim rs As Recordset
Dim strSQL As String
Dim strVolCat As String

Select Case [Forms]![Frm-SelectCompanyVolCat]![Combo94]

Case "1A"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1A")
    strVolCat = "1A"
    
    strSQL = "SELECT DefineCPMVolCatByCustomer.ParentTieID, DefineCPMVolCatByCustomer.Customer, DefineCPMVolCatByCustomer.Volume, DefineCPMVolCatByCustomer.GrossPerTon, DefineCPMVolCatByCustomer.GrossSales, DefineCPMVolCatByCustomer.GTNPerTon,  DefineCPMVolCatByCustomer.GTNSales, DefineCPMVolCatByCustomer.PricePerTon, DefineCPMVolCatByCustomer.NetSales, DefineCPMVolCatByCustomer.COGSPerTon, DefineCPMVolCatByCustomer.COGS, DefineCPMVolCatByCustomer.MarginSales, DefineCPMVolCatByCustomer.MarginPerTon, DefineCPMVolCatByCustomer.MarginPct FROM DefineCPMVolCatByCustomer Where VolCat = '" & strVolCat & "'"
    
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show

End Select

Set rs = Nothing
Set ws = Nothing
Set wb = Nothing
Set xlapp = Nothing

End Sub[/SIZE]

I also did the suggested steps for identifying the variable values, and it just shows the same string that is written in the code.

For example: I hover over strVolCat, and it shows strVolCat = "1A"
& the strQL="SELECT DefineCPMVolCatByCustomer.ParentTieID, ....

I'm sure that the code below is in the correct order, but I must be missing something silly.

Code: 
Option Compare Database
Private Sub cmdExport_Click()

Dim xlapp As Excel.Application
Dim wb As Excel.workbook
Dim ws As Excel.worksheet
Dim rs As Recordset
Dim strSQL As String
Dim strVolCat As String

strSQL = "SELECT DefineCPMVolCatByCustomer.ParentTieID, DefineCPMVolCatByCustomer.Customer, DefineCPMVolCatByCustomer.Volume, DefineCPMVolCatByCustomer.GrossPerTon, DefineCPMVolCatByCustomer.GrossSales, DefineCPMVolCatByCustomer.GTNPerTon,  DefineCPMVolCatByCustomer.GTNSales, DefineCPMVolCatByCustomer.PricePerTon, DefineCPMVolCatByCustomer.NetSales, DefineCPMVolCatByCustomer.COGSPerTon, DefineCPMVolCatByCustomer.COGS, DefineCPMVolCatByCustomer.MarginSales, DefineCPMVolCatByCustomer.MarginPerTon, DefineCPMVolCatByCustomer.MarginPct FROM DefineCPMVolCatByCustomer Where VolCat = '" & strVolCat & "'"

Select Case [Forms]![Frm-SelectCompanyVolCat]![Combo94]

Case "1A"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1A")
    strVolCat = "1A"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show

End Select

Set rs = Nothing
Set ws = Nothing
Set wb = Nothing
Set xlapp = Nothing

End Sub
 
Sevn

Sevn

I trust ME!
Local time
Today, 04:27
Joined
Mar 13, 2008
Messages
97
Hello All,

I wanted to tell all that help on this issue, THANK YOU VERY MUCH!:D

After many hours of staring at the code:eek:, I finally figured out what I was doing wrong. Everything from hyphens(-) in my query names, to trying to trying to create SQL strings w/o variables being set...Duh.

Here is my final WORKING code.

Code: 
Option Compare Database
Private Sub cmdExport_Click()

Dim xlapp As Excel.Application
Dim wb As Excel.workbook
Dim ws As Excel.worksheet
Dim rs As Recordset
Dim strSQL As String
Dim strVolCat As String

Select Case [Forms]![Frm-SelectCompanyVolCat]![Combo94]

Case "1A"
    strVolCat = "1A"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1A")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "1B"
    strVolCat = "1B"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1B")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "1C"
    strVolCat = "1C"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1C")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "1D"
    strVolCat = "1D"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1D")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "2A"
    strVolCat = "2A"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("2A")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "2B"
    strVolCat = "2B"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("2B")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "2C"
    strVolCat = "2C"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("2C")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "2D"
    strVolCat = "2D"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("2D")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case Else
    strVolCat = "1A"

    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1A")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "1B"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("1B")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "1C"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("1C")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "1D"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("1D")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "2A"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("2A")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "2B"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("2B")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "2C"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("2C")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "2D"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("2D")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
         
End Select

Set rs = Nothing
Set ws = Nothing
Set wb = Nothing
Set xlapp = Nothing

End Sub

Thanks again for all your help. I have learned a lot from this experience.
 
M

MRdNk

Registered User.
Local time
Today, 10:27
Joined
Sep 27, 2008
Messages
16
Glad to help, it's good experience to read about others problems and try to solve them, on a bit of a mission to learn Access, then hopefully freelance next year.

You should look at reducing your code, you're repeating a lot of code, that doesn't need to be duplicated; which slows it down.
 
boblarson

boblarson

Smeghead
Local time
Today, 02:27
Joined
Jan 12, 2001
Messages
32,059
This could probably be optimized further but here's a quick attempt at making it less redundant:
Code: 
Option Compare Database
Private Sub cmdExport_Click()

Dim xlapp As Excel.Application
Dim wb As Excel.workbook
Dim ws As Excel.worksheet
Dim rs As Recordset
Dim strSQL As String
Dim strVolCat As String

Select Case [Forms]![Frm-SelectCompanyVolCat]![Combo94]

strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany
Set xlapp = New Excel.Application
Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")

Case "1A"
    strVolCat = "1A"
    
Case "1B"
    strVolCat = "1B"
    
Case "1C"
    strVolCat = "1C"
    
Case "1D"
    strVolCat = "1D"
    
Case "2A"
    strVolCat = "2A"
    
Case "2B"
    strVolCat = "2B"
    
Case "2C"
    strVolCat = "2C"
    
Case "2D"
    strVolCat = "2D"
    
Case Else ' Copy ALL 
    strVolCat = "1A"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set ws = wb.worksheets(strVolCat)
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "1B"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set ws = wb.worksheets(strVolCat)
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "1C"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set ws = wb.worksheets(strVolCat)
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "1D"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set ws = wb.worksheets(strVolCat)
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "2A"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set ws = wb.worksheets(strVolCat)
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "2B"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set ws = wb.worksheets(strVolCat)
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "2C"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set ws = wb.worksheets(strVolCat)
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "2D"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
    Set ws = wb.worksheets(strVolCat)
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    GoTo ExitMySub
         
End Select

	strSQL = strSQL & " Where VolumeCat = '" & strVolCat & "'"
    	Set ws = wb.worksheets(strVolCat)
    	Set rs = CurrentDb.OpenRecordset(strSQL)
	ws.range("A2").copyfromrecordset rs

ExitMySub:
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
Set rs = Nothing
Set ws = Nothing
Set wb = Nothing
Set xlapp = Nothing

End Sub
 
Sevn

Sevn

I trust ME!
Local time
Today, 04:27
Joined
Mar 13, 2008
Messages
97
Hello,

I new there was an easier way. You guys just wanted me to figure it out the hard way first. LOL!

I will try it out, and see what happens.

Thanks again for all your help...
 
M

MRdNk

Registered User.
Local time
Today, 10:27
Joined
Sep 27, 2008
Messages
16
Hopefully this'll work, let me know if you have any issues.

Are you select all fields & data from your table?
If so replace the strSQL with:
strSQL = "SELECT * FROM ExportTblCPMByCompany"

A lot easier to write & read.

Try it:
Code: 
Private Sub cmdExport_Click()

Dim xlapp As Excel.Application
Dim wb As Excel.workbook
Dim ws As Excel.worksheet
Dim rs As Recordset
Dim strSQL As String
Dim strVolCat As String

Dim asRef(7) As String ' Array of 8 values (as - array string) - Added by MRdNk
Dim i As Integer    ' integer used as a counter, for the array - added by MRdNk
Dim bCopyAll As Boolean ' True equals - Copy All
    bCopyAll = False
    
Select Case [Forms]![Frm-SelectCompanyVolCat]![Combo94]

strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany"
Set xlapp = New Excel.Application
Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")

'''''''''''''''''
''''MRdNk bit''''
'''''''''''''''''
       
    asRef(0) = "A1"
    asRef(1) = "A2"
    asRef(2) = "A3"
    asRef(3) = "A4"
    asRef(4) = "B1"
    asRef(5) = "B2"
    asRef(6) = "B3"
    asRef(7) = "A4"

    Case "1A"
        strVolCat = "1A"
    
    Case "1B"
        strVolCat = "1B"
        
    Case "1C"
        strVolCat = "1C"
        
    Case "1D"
        strVolCat = "1D"
        
    Case "2A"
        strVolCat = "2A"
        
    Case "2B"
        strVolCat = "2B"
        
    Case "2C"
        strVolCat = "2C"
        
    Case "2D"
        strVolCat = "2D"
        
    Case Else ' Copy ALL
        bCopyAll = True
    
    
        For i = 0 To 7
            If bCopyAll = True Then
                If i = 0 Then
                    strVolCat = asRef(i)
                Else
                    strVolCat = strVolCat & ", " & asRef(i)
                End If
            Else
                i = 7
            End If
            
            strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "'"
            
            Set ws = wb.worksheets(strVolCat)
            Set rs = CurrentDb.OpenRecordset(strSQL)
            ws.range("A2").copyfromrecordset rs
    
        Next i

        bCopyAll = False
        
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
    Set rs = Nothing
    Set ws = Nothing
    Set wb = Nothing
    Set xlapp = Nothing

End Sub
 
Last edited:
M

MRdNk

Registered User.
Local time
Today, 10:27
Joined
Sep 27, 2008
Messages
16
Ps. I add my comments and name, to show you where I changed it, please feel free to remove them.
 
Sevn

Sevn

I trust ME!
Local time
Today, 04:27
Joined
Mar 13, 2008
Messages
97
Hello again,

Thanks for the simplified code bits, but both are returning the same error.
Compile Error: Statements and labels between Select Case and first Case.

In addition; there is actually a second combo box that designates company, and I will need to work it into the code. The combo box has 3 options: NASC, Sifto, ALL.

Originally I was just going to put the combo box as a parameter in a query, but now (after all the revisions) my code is querying against a temp table. Therefore; I can't just put the combo box in as a parameter.

So, back to what I need to do.

Currently; I have code that queries & exports ALL records based on the VolCat combo box.
I have a company combo box: [Forms]![Frm-SelectCompanyVolCat]![Combo78]; Values: ALL, NASC, Sifto
Here's the way I thought it should be, but it isn't working. Also; as I am typing, I just realized that the way I have it won't recognize the ALL portion. How can i add in this combo box criteria into my existing code.

Code: 
Option Compare Database
Private Sub cmdExport_Click()

Dim xlapp As Excel.Application
Dim wb As Excel.workbook
Dim ws As Excel.worksheet
Dim rs As Recordset
Dim strSQL As String
Dim strVolCat As String
[COLOR="Red"][B]Dim strCompany As String[/B][/COLOR]

Select Case [Forms]![Frm-SelectCompanyVolCat]![Combo94]

Case "1A"
    [COLOR="Red"][B]strCompany = "[Forms]![Frm-SelectCompanyVolCat]![Combo78]"[/B][/COLOR]
    strVolCat = "1A"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany [COLOR="Red"][B]Where VolumeCat = '" & strVolCat & "', Company = '" & strCompany & "'[/B][/COLOR] ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1A")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "1B"
    strVolCat = "1B"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1B")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "1C"
    strVolCat = "1C"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1C")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "1D"
    strVolCat = "1D"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1D")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "2A"
    strVolCat = "2A"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("2A")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "2B"
    strVolCat = "2B"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("2B")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "2C"
    strVolCat = "2C"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("2C")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "2D"
    strVolCat = "2D"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("2D")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case Else
    strVolCat = "1A"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1A")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "1B"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("1B")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "1C"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("1C")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "1D"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("1D")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "2A"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("2A")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "2B"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("2B")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "2C"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("2C")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "2D"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("2D")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
         
End Select

Set rs = Nothing
Set ws = Nothing
Set wb = Nothing
Set xlapp = Nothing

End Sub
 
Sevn

Sevn

I trust ME!
Local time
Today, 04:27
Joined
Mar 13, 2008
Messages
97
Hello All,

I have some working code that I would like to clean up. I'm sure there is a better way to write this, but I'm not very experienced in VBA yet. Please forward any suggestions that you may have.

Sample Code:
Code: 
Option Compare Database
Private Sub cmdExport_Click()

'Backup 20081003

Dim xlapp As Excel.Application
Dim wb As Excel.workbook
Dim ws As Excel.worksheet
Dim rs As Recordset
Dim strSQL As String
Dim strVolCat As String
Dim strCompany As String

strCompany = [Forms]![Frm-SelectCompanyVolCat]![Combo78]

Select Case [Forms]![Frm-SelectCompanyVolCat]![Combo94]

Case "1A"
    strVolCat = "1A"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' AND Company = '" & strCompany & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1A")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "1B"
    strVolCat = "1B"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' AND Company = '" & strCompany & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1B")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "1C"
    strVolCat = "1C"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' AND Company = '" & strCompany & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1C")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "1D"
    strVolCat = "1D"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' AND Company = '" & strCompany & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1D")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "2A"
    strVolCat = "2A"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' AND Company = '" & strCompany & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("2A")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "2B"
    strVolCat = "2B"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' AND Company = '" & strCompany & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("2B")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "2C"
    strVolCat = "2C"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' AND Company = '" & strCompany & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("2C")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case "2D"
    strVolCat = "2D"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' AND Company = '" & strCompany & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("2D")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
    
Case Else
    strVolCat = "1A"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' AND Company = '" & strCompany & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Open("C:\Documents and Settings\youngje\Desktop\Project0020-CustomerPricingMatrix\TEMPLATE-CPMByCompany.xltm")
    Set ws = wb.worksheets("1A")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "1B"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' AND Company = '" & strCompany & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("1B")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "1C"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' AND Company = '" & strCompany & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("1C")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "1D"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' AND Company = '" & strCompany & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("1D")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "2A"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' AND Company = '" & strCompany & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("2A")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "2B"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' AND Company = '" & strCompany & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("2B")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "2C"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' AND Company = '" & strCompany & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("2C")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    strVolCat = "2D"
    strSQL = "SELECT ExportTblCPMByCompany.ParentTieID, ExportTblCPMByCompany.Customer, ExportTblCPMByCompany.Volume, ExportTblCPMByCompany.GrossPerTon, ExportTblCPMByCompany.GrossSales, ExportTblCPMByCompany.GTNPerTon, ExportTblCPMByCompany.GTNSales, ExportTblCPMByCompany.PricePerTon, ExportTblCPMByCompany.NetSales, ExportTblCPMByCompany.COGSPerTon, ExportTblCPMByCompany.COGS, ExportTblCPMByCompany.MarginSales, ExportTblCPMByCompany.MarginPerTon, ExportTblCPMByCompany.MarginPct FROM ExportTblCPMByCompany Where VolumeCat = '" & strVolCat & "' AND Company = '" & strCompany & "' ORDER BY ExportTblCPMByCompany.MarginPct DESC"
    Set wb = xlapp.ActiveWorkbook
    Set ws = wb.worksheets("2D")
    Set rs = CurrentDb.OpenRecordset(strSQL)
    ws.range("A2").copyfromrecordset rs
    
    xlapp.Visible = True
    xlapp.Dialogs(xlDialogSaveAs).Show
         
End Select

Set rs = Nothing
Set ws = Nothing
Set wb = Nothing
Set xlapp = Nothing

End Sub
 
M

MRdNk

Registered User.
Local time
Today, 10:27
Joined
Sep 27, 2008
Messages
16
Can you post a copy of your DB without any sensitive data?
 
M

MRdNk

Registered User.
Local time
Today, 10:27
Joined
Sep 27, 2008
Messages
16
If you use mine, except use this for the case:

Move this:
Select Case [Forms]![Frm-SelectCompanyVolCat]![Combo94]
to here:

Code: 
Select Case [Forms]![Frm-SelectCompanyVolCat]![Combo94]

    Case "1A"
        strVolCat = "1A"
    
    Case "1B"
        strVolCat = "1B"
        
    Case "1C"
        strVolCat = "1C"
        
    Case "1D"
        strVolCat = "1D"
        
    Case "2A"
        strVolCat = "2A"
        
    Case "2B"
        strVolCat = "2B"
        
    Case "2C"
        strVolCat = "2C"
        
    Case "2D"
        strVolCat = "2D"
        
    Case Else ' Copy ALL
        bCopyAll = True
   
    End Select

And add the "End Select"
 
M

MRdNk

Registered User.
Local time
Today, 10:27
Joined
Sep 27, 2008
Messages
16
Please let me know if this works?
 
Sevn

Sevn

I trust ME!
Local time
Today, 04:27
Joined
Mar 13, 2008
Messages
97
Hello,

Here's a sample of my DB. I stripped out everything not related to this procedure, and disabled the Print Preview button that I have on one of my form because it runs off a query not included in the sample.
 

Attachments

  • Sample.zip
    353.7 KB · Views: 169
M

MRdNk

Registered User.
Local time
Today, 10:27
Joined
Sep 27, 2008
Messages
16
Oh its Access 2007, can't help unless you make a copy of that file, and convert it back to 2003. This procedure should work in both. Otherwise someone else might be able to help you.
Did you try my latest suggestion? And what error is it giving, if it doesn't work?
 
Sevn

Sevn

I trust ME!
Local time
Today, 04:27
Joined
Mar 13, 2008
Messages
97
Hello,

I have not tried your new suggestion, for my code has changed many times since your first suggestion was originally posted. I currently have my tool working, and will have to modify your code to work with the current code that I have.

I have posted a 2003 DB for your review.
 

Attachments

  • Sample.zip
    370.7 KB · Views: 180
You must log in or register to reply here.

Similar threads

W
  • Question
Solved Excel xls-Export
Replies
6
Views
337
wanzi
W
S
Solved Export from Ms Access form in to Excel
Replies
5
Views
304
skdn71
S
raziel3
Solved How to Export Current Recordset and remove certain fields to Excel (Field/Column Filtering)
2
Replies
35
Views
1,488
theDBguy
theDBguy
G
DoCmd.OutputTo - Excel coded sql string
Replies
9
Views
311
Gasman
Gasman
T
Solved the expression you entered is too complex
2
Replies
37
Views
468
Pat Hartman
P

Users who are viewing this thread

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