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
LVL 4
billcuteAsked:
Who is Participating?
 
bonjour-autCommented:
Hi Bill,

in my example, i showed two complete different method to go for the excel file

one using the granular control

and the other one using the OutputTo-Command from the temporary table. The reason for the temporary table is, that you cannot use a template with this and therefore the final excel file will have the fieldnames of the datasource as column-names. furtheron this method cannot produce a excelfile which has fields in different rows.

so the granular method is left. as we only need one record, we can skip the Do While ... Loop
Furtheron for the same reason we can adress the rownumbers directly instead of a myRow variable

so the actual code between   'With Wkb' .. and ... 'End With'  will be:

mysql = "SELECT ShutOffID,AppDate,BlockNo,LotNo,HouseNo,StreetName,Address,CurbDistID,CurbDirID,MainSizeID,MainTypeID FROM tblShutOff WHERE ((ShutOffID=" & Me.ShutOffID & "));"
    Set rs = CurrentDb.OpenRecordset(mysql) 'corresponding to your last SQL-post
    rs.MoveLast
    rs.MoveFirst
      .Worksheets(1).Cells(2, 1).Value = Format(rs.Fields("AppDate"), "yyyymmdd")
      .Worksheets(1).Cells(2, 2).Value = rs.Fields("BlockNo")
      .Worksheets(1).Cells(2, 3).Value = rs.Fields("LotNo")
      .Worksheets(1).Cells(2, 6).Value = rs.Fields("HouseNo")
      .Worksheets(1).Cells(2, 6).Value = rs.Fields("StreetName")
      .Worksheets(1).Cells(2, 7).Value = rs.Fields("Address")
      .Worksheets(1).Cells(2, 8).Value = rs.Fields("ShutOffID")
      .Worksheets(1).Cells(5, 1).Value = rs.Fields("CurbDistID")
      .Worksheets(1).Cells(5, 2).Value = rs.Fields("CurbDirID")
      .Worksheets(1).Cells(8, 1).Value = rs.Fields("MainSizeID")
      .Worksheets(1).Cells(8, 2).Value = rs.Fields("MainTypeID")
    rs.Close


Regards, Franz

0
 
billcuteAuthor Commented:
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
0
 
bonjour-autCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
billcuteAuthor Commented:
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 & "));"
0
 
bonjour-autCommented:
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

0
 
billcuteAuthor Commented:
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
0
 
billcuteAuthor Commented:
Franz,
Thanks for your assistance. I got it working now.

Regards
Bill
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.