[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel Output  to other marked cells

Posted on 2006-03-30
7
Medium Priority
?
292 Views
Last Modified: 2008-02-26
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
0
Comment
Question by:billcute
  • 4
  • 3
7 Comments
 
LVL 18

Accepted Solution

by:
bonjour-aut earned 2000 total points
ID: 16341956
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
 
LVL 4

Author Comment

by:billcute
ID: 16342033
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
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 16347759
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:billcute
ID: 16356669
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
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 16360095
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
 
LVL 4

Author Comment

by:billcute
ID: 16366439
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
 
LVL 4

Author Comment

by:billcute
ID: 16377682
Franz,
Thanks for your assistance. I got it working now.

Regards
Bill
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question