Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
combine code to get data in one sheet
Hi All,
The follwing is the query code what i use in my sheet1 and sheet2 Can I combine this code and get both data in my sheet1 instead of sheet1 and sheet2? I am taking identical fields from both tables as these tables are kept for cash and credit and there are a few un identical fields in these two tables. With Selection.QueryTable .Connection = _ "ODBC;DRIVER=SQL Server;SERVER=abc.com;UID=User;PWD=pwd;APP=Microso ft Office 2003;WSID=mycom" ..CommandText = Array( _ "SELECT Cash.arl, Cash.Tktno, Cash.tkttype, Cash.bsp, Cash.SalRef, Cash.Issuedate, Cash.class, Cash.Reissueno, Cash.Routeovrall, Cash.PassName, Cash.IRNum, Cash.P" _ , _ "ubFare, Cash.ComFare, Cash.fd, Cash.My_fd, Cash.unpaid, Cash.Rev, Cash.tax1, Cash.tax2, Cash.tax3, Cash.FuelSurcharge, Cash.slsman, " _ , _ "Cash.Txino, Cash.AddColl, Cash.MyDscntVal, Cash.Target, Cash.Stock, Cash.SpComVal, Cash.Net2Air, Cash.CashReceipt, Cash.TourCode, " _ , _ "Cash.Remarks, Cash.Commissi" _ , _ "onable, Cash.Face, Cash.prov" & Chr(13) & "" & Chr(10) & "FROM Attar_Travel_2004.dbo.Cash Cash" & Chr(13) & "" & Chr(10) & "WHERE (Cash.SalRef<'U') AND (Cash.Tktno Not Like '%I%')" _ , _ " AND (Cash.Issuedate={ts '" & Dt1 & " 00:00:00'} And Cash.Issuedate<={ts '" & Dt2 & " 00:00:00'})") ..Refresh BackgroundQuery:=False End With With Selection.QueryTable .Connection = _ "ODBC;DRIVER=SQL Server;SERVER=abc.com;UID=User;PWD=pwd;APP=Microso ft Office 2003;WSID=mycom" .CommandText = Array( _ "SELECT Credit.arl, Credit.Tktno, Credit.tkttype, Credit.bsp, Credit.SalRef, Credit.Issuedate, Credit.class, Credit.Reissueno, Credit.Routeovrall, Credit.PassName, Credit.IRNum, Credit.P" _ , _ "ubFare, Credit.ComFare, Credit.fd, Credit.My_fd, Credit.unpaid, Credit.Rev, Credit.tax1, Credit.tax2, Credit.tax3, Credit.FuelSurcharge, Credit.slsman, " _ , _ "Credit.Txino, Credit.AddColl, Credit.MyDscntVal, Credit.Target, Credit.Stock, Credit.SpComVal, Credit.Net2Air, Credit.CreditReceipt, Credit.TourCode, " _ , _ "Credit.Remarks, Credit.Commissi" _ , _ "onable, Credit.Face, Credit.prov" & Chr(13) & "" & Chr(10) & "FROM Attar_Travel_2004.dbo.Credit Credit" & Chr(13) & "" & Chr(10) & "WHERE (Credit.SalRef<'U') AND (Credit.Tktno Not Like '%I%')" _ , _ " AND (Credit.Issuedate={ts '" & Dt1 & " 00:00:00'} And Cedit.Issuedate<={ts '" & Dt2 & " 00:00:00'})") .Refresh BackgroundQuery:=False End With TIA Soniya |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
combine code to get data in one sheet
Sonya, as answered to an earlier post on the same subject
you'll have to use the UNION. if you want two fields with different names to be selected in the same field just alias it. A simple test on Northwind.. SELECT CompanyName,ShipperID as ID FROM Shippers UNION SELECT CompanyName, SupplierID AS ID FROM Suppliers If you had taken the time to format your snippet so that people can easily cut and paste it from a newsreader.. MAKE SURE the lines are no longer than 70 chars (which allows for a bit of space on quoted replies..) ANd if you record these macro;s.. take some time to edit the code so that it's readable (and fields names are not cutoff halfway in the string) However the union string was too long for line continuance so i've created the commandtext array in a different manner. Cleaned code UNTESTED.. Sub Sonya() Dim sCmd() Dim dt1 As Date Dim dt2 As Date Dim i% ReDim sCmd(8) sCmd(0) = _ " SELECT " & _ " c.arl, c.Tktno, c.tkttype, c.bsp,c.SalRef, c.Issuedate," & _ " c.class, c.Reissueno,c.Routeovrall, c.PassName, c.IRNum, " & _ " c.PubFare, c.ComFare, c.fd, c.My_fd, c.unpaid,c.Rev," & _ " c.tax1, c.tax2, c.tax3, c.FuelSurcharge , c.slsman, " sCmd(1) = _ " c.Txino, c.AddColl, c.MyDscntVal, c.Target, c.Stock, " & _ " c.SpComVal, c.Net2Air,c.CashReceipt as Receipt, c.TourCode, " & _ " c.Remarks, c.Commissionable, c.Face, c.prov" & vbCrLf sCmd(2) = _ " FROM Attar_Travel_2004.dbo.Cash c" & vbCrLf sCmd(3) = _ " WHERE (c.SalRef<'U') AND (c.Tktno Not Like '%I%')" & _ " AND (c.Issuedate={ts '" & dt1 & " 00:00:00'} " & _ " AND c.Issuedate<={ts '" & dt2 & " 00:00:00'})" & vbCrLf sCmd(4) = " UNION " & vbCrLf sCmd(5) = sCmd(0) sCmd(6) = Replace(sCmd(1), ".CashReceipt", ".CreditReceipt") sCmd(7) = Replace(sCmd(2), ".Cash", ".Credit") sCmd(8) = sCmd(3) For i = 0 To 8 Debug.Assert Len(sCmd(i)) <= 255 Next With Selection.QueryTable .Connection = _ "ODBC;DRIVER=SQL Server;SERVER=abc.com;UID=User;" & _ "PWD=pwd;APP=Microsoft Office 2003;WSID=mycom" .CommandText = sCmd .Refresh BackgroundQuery:=False End With End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Soniya" wrote: Hi All, The follwing is the query code what i use in my sheet1 and sheet2 Can I combine this code and get both data in my sheet1 instead of sheet1 and sheet2? I am taking identical fields from both tables as these tables are kept for cash and credit and there are a few un identical fields in these two tables. <SNIP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
combine code to get data in one sheet
Hi Dear keepITcool
Thank you very much for your kind reply. Even though you had answered my query previously still it confused me a little and i thought it may not work for me. Thanks again for your repeated post Soniya -----Original Message----- Sonya, as answered to an earlier post on the same subject you'll have to use the UNION. if you want two fields with different names to be selected in the same field just alias it. A simple test on Northwind.. SELECT CompanyName,ShipperID as ID FROM Shippers UNION SELECT CompanyName, SupplierID AS ID FROM Suppliers If you had taken the time to format your snippet so that people can easily cut and paste it from a newsreader.. MAKE SURE the lines are no longer than 70 chars (which allows for a bit of space on quoted replies..) ANd if you record these macro;s.. take some time to edit the code so that it's readable (and fields names are not cutoff halfway in the string) However the union string was too long for line continuance so i've created the commandtext array in a different manner. Cleaned code UNTESTED.. Sub Sonya() Dim sCmd() Dim dt1 As Date Dim dt2 As Date Dim i% ReDim sCmd(8) sCmd(0) = _ " SELECT " & _ " c.arl, c.Tktno, c.tkttype, c.bsp,c.SalRef, c.Issuedate," & _ " c.class, c.Reissueno,c.Routeovrall, c.PassName, c.IRNum, " & _ " c.PubFare, c.ComFare, c.fd, c.My_fd, c.unpaid,c.Rev," & _ " c.tax1, c.tax2, c.tax3, c.FuelSurcharge , c.slsman, " sCmd(1) = _ " c.Txino, c.AddColl, c.MyDscntVal, c.Target, c.Stock, " & _ " c.SpComVal, c.Net2Air,c.CashReceipt as Receipt, c.TourCode, " & _ " c.Remarks, c.Commissionable, c.Face, c.prov" & vbCrLf sCmd(2) = _ " FROM Attar_Travel_2004.dbo.Cash c" & vbCrLf sCmd(3) = _ " WHERE (c.SalRef<'U') AND (c.Tktno Not Like '%I%')" & _ " AND (c.Issuedate={ts '" & dt1 & " 00:00:00'} " & _ " AND c.Issuedate<={ts '" & dt2 & " 00:00:00'})" & vbCrLf sCmd(4) = " UNION " & vbCrLf sCmd(5) = sCmd(0) sCmd(6) = Replace(sCmd (1), ".CashReceipt", ".CreditReceipt") sCmd(7) = Replace(sCmd(2), ".Cash", ".Credit") sCmd(8) = sCmd(3) For i = 0 To 8 Debug.Assert Len(sCmd(i)) <= 255 Next With Selection.QueryTable .Connection = _ "ODBC;DRIVER=SQL Server;SERVER=abc.com;UID=User;" & _ "PWD=pwd;APP=Microsoft Office 2003;WSID=mycom" .CommandText = sCmd .Refresh BackgroundQuery:=False End With End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Soniya" wrote: Hi All, The follwing is the query code what i use in my sheet1 and sheet2 Can I combine this code and get both data in my sheet1 instead of sheet1 and sheet2? I am taking identical fields from both tables as these tables are kept for cash and credit and there are a few un identical fields in these two tables. <SNIP . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i combine data from multiple sheets into one sheet? | Excel Discussion (Misc queries) | |||
combine data into third sheet | Excel Worksheet Functions | |||
how do I transfer data from one sheet to another with VB code | Excel Discussion (Misc queries) | |||
How do I combine data from several sheets into one main sheet? | Excel Worksheet Functions | |||
Combine Code | Excel Programming |