asked on

Excel Output to other marked cells

How can I modify my current code to "OUTPUT" to excell also on the second row using my Excel template "ShutOffDetails.xlt" (template) further and added header fields for:
(a). CurbDistID @ cell A4 (b). CurbDirID @ cell B4
(c). MainSizeID @ cell A7 (d). MainTypeID @ cell B7

See me template at the link below:
http://www.geocities.com/bombastikbill/Excel_Template.zip

My current design defined variable myrow... Dim myrow"

and then modify tbl_tmp and mysql as shown below:
mysql = "SELECT ShutOffID,AppDate,BlockNo,LotNo,HouseNo,StreetName,Address,CurbDistID,CurbDirID,MainSizeID,MainTypeID, FROM tblShutOff WHERE ((ShutOffID=" & Me.ShutOffID & "));"

...and tbl_tmp as:
mysql = "INSERT INTO tbl_tmp SELECT ShutOffID,AppDate,BlockNo,LotNo,HouseNo,StreetName,Address,CurbDistID,CurbDirID,MainSizeID,MainTypeID, FROM tblShutOff WHERE ((ShutOffID=" & Me.ShutOffID & "));"

How can I display the additional data fields for CurbDistID and CurbDirID at cells A5 and B5 respectively ?

Similarly, how can I display additional fiields for MainSizeID and MainTypeID at cells A8 and B8 respectively ?

Here is the current working code I use that display display one row.

All I need here is just to modify worksheet (1) code to include the additional fields mentioned above:

' .......................................
'.......................................
Set Wkb = objXL.Application.Workbooks.Open(myXLT)
With Wkb
mysql = "SELECT ShutOffID,AppDate,BlockNo,LotNo,HouseNo,StreetName,Address FROM tblShutOff WHERE ((ShutOffID=" & Me.ShutOffID & "));"
myRow = 2
Set rs = CurrentDb.OpenRecordset(mysql) 'corresponding to your last SQL-post
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
.Worksheets(1).Cells(myRow, 1).Value = Format(rs.Fields("AppDate"), "yyyymmdd")
.Worksheets(1).Cells(myRow, 2).Value = rs.Fields("BlockNo")
.Worksheets(1).Cells(myRow, 3).Value = rs.Fields("LotNo")
.Worksheets(1).Cells(myRow, 4).Value = rs.Fields("HouseNo")
.Worksheets(1).Cells(myRow, 5).Value = rs.Fields("StreetName")
.Worksheets(1).Cells(myRow, 6).Value = rs.Fields("Address")
.Worksheets(1).Cells(myRow, 7).Value = rs.Fields("ShutOffID")
rs.MoveNext
myRow = myRow + 1
Loop

ASKER CERTIFIED SOLUTION

bonjour-aut

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

billcute

ASKER

Franz,
Thanks for the input....in one of your other similar posts...you had the code below.

' ...........................
' ............................
rs.MoveNext
myRow = myRow + 1
Loop
End With
Wkb.Close savechanges:=True
If boolXL Then objXL.Application.Quit
Set objActiveWkb = Nothing: Set objXL = Nothing
End Sub

Now that you ended this post with "rs.Close" instead of "rs.MoveNext" and so on...

How do I join the remaining code listed above?

Regards
Bill

bonjour-aut

if you just retrieve one record, you do not need the Loop

so the complete finish to my last post would be:

...
...
.Worksheets(1).Cells(8, 2).Value = rs.Fields("MainTypeID")
rs.Close
End With
Wkb.Close savechanges:=True
If boolXL Then objXL.Application.Quit
Set objActiveWkb = Nothing: Set objXL = Nothing
End Sub

Regards, Franz

billcute

ASKER

I made all the necessary corrections per your last two post. Upon clicking on "OUTPUT TO EXCEL IN GRANULAR VBA"...."Private Sub WriteToExcel",

...I received:
Run-time error '3061': Too few parametesrs: Expected 1

...and debug highlighted:
Set rs = CurrentDb.OpenRecordset(mysql) 'corresponding to your last SQL-post

(2).
I also clicked on the second button "USING THE OUTPUT TO COMMAND....."
I received:
Run-time error '1334':
Syntax error in INSERT into statement

...and debug highlighted:
DoCmd.RunSQL (mysql)

Here is my new myql for that command button "Private Sub OutputTo_Click".

mysql = "INSERT INTO tbl_tmp SELECT ShutOffID,AppDate,BlockNo,LotNo,HouseNo,StreetName,Address,CurbDistID,CurbDirID,MainSizeID,MainTypeID, FROM tblShutOff WHERE ((ShutOffID=" & Me.ShutOffID & "));"

bonjour-aut

hi Bill

ad 1) please check your tables and your sql - in your first post you have a semicolon after the last field (MainTypeID)
the correct sytax is: "Select Field1, Field2, Field3 From Datasource Where((...))"

ad 2) You cannot use the Output to command - it will not let you place the fields - you will get a excel, where all fiels are in consecutive columns. also here the semicolon after MainTypeID is the reason for the error message.

if you do not get along, i will mail you the working example.

regards, Franz

billcute

ASKER

Franz,
I did correct the syntax as suggested in your last post but I am still getting the two errors earlier mentioned in my previous post. If you would be kind to send me your sample. I'll be grateful.

Regards
Bill

billcute

ASKER

Franz,
Thanks for your assistance. I got it working now.

Regards
Bill