VB and Excel 2000 interaction
-
I have written 1 app which reads data from access table and writes it to existing excel sheet . Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open(App.Path & "\ben_aux.xls") xlBook.Sheets(8).Activate xlBook.Sheets(8).Name = "MS 2-Nt Rev" sqlString = "SELECT RESHDR.ArrivalDt as Date, Reshdr.MktSegID, Sum(Reshdr.RoomRev)" _ & "From Reshdr" _ & "Where Reshdr.LOS = 2" _ & "GROUP BY Reshdr.ArrivalDt, Reshdr.MktSegId" With xlBook.ActiveSheet.QueryTables.Add(Connection:=connstring, _ Destination:=Range("A6"), Sql:=sqlString) .Refresh End With I am getting following error. the destination range is not on the same worksheet that the query table is being created on. using VB6.0 and Excel 2000 and access 2000
-
I have written 1 app which reads data from access table and writes it to existing excel sheet . Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open(App.Path & "\ben_aux.xls") xlBook.Sheets(8).Activate xlBook.Sheets(8).Name = "MS 2-Nt Rev" sqlString = "SELECT RESHDR.ArrivalDt as Date, Reshdr.MktSegID, Sum(Reshdr.RoomRev)" _ & "From Reshdr" _ & "Where Reshdr.LOS = 2" _ & "GROUP BY Reshdr.ArrivalDt, Reshdr.MktSegId" With xlBook.ActiveSheet.QueryTables.Add(Connection:=connstring, _ Destination:=Range("A6"), Sql:=sqlString) .Refresh End With I am getting following error. the destination range is not on the same worksheet that the query table is being created on. using VB6.0 and Excel 2000 and access 2000
Are you simply trying to place the result sets on a worksheet? I wrote a sample of this a while ago, using VB6 + Access w/ Excel. Check it out: http://hobbitwerk.brinkster.net/vbSamples/MoreEXcelVBA.zip *->>Always working on my game, teach me *->>something new. cout << "dav1d\n";
-
Are you simply trying to place the result sets on a worksheet? I wrote a sample of this a while ago, using VB6 + Access w/ Excel. Check it out: http://hobbitwerk.brinkster.net/vbSamples/MoreEXcelVBA.zip *->>Always working on my game, teach me *->>something new. cout << "dav1d\n";
Ya I am executing query and putting result set in to excel sheet .I provide start range for data on excel sheet. Thanks for ur help....I could write the data on sheet. 1 Question. In query I am calculating Sum and when i am putting the sum on excel sheet I put it like : sum(abc) as 'xyz'. If i put like sum( abc ) as xyz (w/o single quotes) it gives me error (SQL syntax error ).