|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
July 13th, 2006, 08:42 PM
|
||||
|
||||
|
Excel Application
Ive witten code to connect to my db and write out packing slips to an excel file, 1 packing slip to Each sheet. My problem is that the sheets are coming in as.
Sheet # | Packing Slip # | Arrangement in Exel 4 | 2 | 1 5 | 3 | 2 6 | 4 | 3 7 | 5 | 4 8 | 6 | 5 9 | 7 | 6 10 | 8 | 7 1 | 0 | 8 2 | 1 | 9 3 | 9 | 10 Instead of having Packing Slip 1 on Sheet 1 order 1 - 10, So it prints out the Pslips 1-10. Is there a Sheet Move Function, and any reason why 9 is coming on the last page. Heres my code: Code:
strsheetcount = 1
xlrow = 1
master_file = "C:\Inetpub\WebSites\***\test.xls"
save_as_file = "C:\Inetpub\WebSites\***\output.xls"
Set xl = CreateObject("Excel.Application")
xl.Visible = False
xl.DisplayAlerts = False
xl.Interactive = False
Set wrk = xl.Workbooks.Open(master_file)
'Set wrkSht = wrk.sheets(1)
'Set to Sheet1 because that is excel default
strsql = "Select * from tb_orders where status='Step 1'"
rsitems.open strsql, adocon
Do While Not Rsitems.EOF
Set wrkSht = wrk.sheets(strsheetcount)
stritemcount = 0
'-------------------------------------------------------------------------
wrkSht.Cells(7, 1).Value = "#" & rsitems("order_ID")
wrkSht.Cells(8, 1).Value = "Date:"
wrkSht.Cells(9, 1).Value = "Ship to:"
wrkSht.Cells(13, 1).Value = "Via:"
wrkSht.Cells(15, 1).Value = "Item"
wrkSht.Cells(15, 8).Value = "Code"
wrkSht.Cells(15, 10).Value = "Qty"
wrkSht.Cells(7, 3).Value = "Packing Slip" & rsitems("order_ID") & " for ***"
wrkSht.Cells(8, 3).Value = now
wrkSht.Cells(9, 3).Value = rsitems("s_firstname") & " " & rsitems("s_lastname")
wrkSht.Cells(10, 3).Value = rsitems("s_address")
wrkSht.Cells(11, 3).Value = rsitems("s_city") & " " & rsitems("s_state") & ", " & rsitems("s_zip")
wrkSht.Cells(13, 3).Value = strshipping
strsql2 = "Select * from tb_sold where order_id='" & rsitems("order_ID") & "'"
rsitems2.open strsql2, adocon
Do While Not Rsitems2.EOF
wrkSht.Cells(16 + cint(stritemcount), 1).Value = rsitems2("Item_Name")
wrkSht.Cells(17 + cint(stritemcount), 1).Value = "http://***/?itemnum=" & rsitems2("Item_ID")
wrkSht.Cells(16 + cint(stritemcount), 8).Value = rsitems2("Item_ID")
wrkSht.Cells(16 + cint(stritemcount), 10).Value = rsitems2("qty")
stritemcount = stritemcount + 2
rsitems2.movenext
loop
xlrow = xlrow + 1
rsitems2.close
rsitems.movenext
If NOT rsItems.EOF then 'IF THERE IS ANOTHER RECORD, COPY/PASTE
'----------------------------------------------------------------------------------------
' PASTE NEW SHEET
'-----------------------------------------------------------------------------------------
strsheetcount = strsheetcount + 1
If strsheetcount > 3 Then
wrk.sheets.add()
End If
'Set wrkSht = wrk.sheets("Sheet1") 'SELECT THE ORIGINAL
wrk.sheets("Sheet1").Select
wrkSht.Cells.Copy 'COPY CELLS FROM 1st
Set wrkSht = wrk.sheets("Sheet" & strsheetcount) 'Set 2nd wrkSht object to sheet2
'wrkSht.Cells.Select
wrkSht.Paste 'Paste the copied text
xl.CutCopyMode = False 'I have no idea what this means
End If
loop
rsitems.close
adocon.close
set rsitems = nothing
set rsitems2 = nothing
set adocon = nothing
'----------------------------------
' SAVE MASTER FILE & QUIT
'----------------------------------
wrk.SaveAs save_as_file 'THIS SECTION CAN BE USED TO SET FILE TYPE
'----------------------------------------------------------------------------
' DIFFERENT FILE TYPES
'------------------------------------------------------------------------------
'wrk.SaveAs ave_as_file, -4158 'Save as TAB DELIMITED File
'wrk.SaveAs ave_as_file, 6 'Save as a CSV File
wrk.Close
Set wrkSht = Nothing
Set wrk = Nothing
xl.Quit
Set xl = Nothing
Wscript.echo "Done."
__________________
That's 'cause droids don't pull people's arms out of their sockets when they lose. Wookies are known to do that. Last edited by Shadow Wizard : September 18th, 2006 at 06:43 AM.
|
|
#2
July 14th, 2006, 01:02 AM
|
|||
|
|||
|
you can control where your new sheet gets added with the optional parameters.
wrk.sheets.add([Before], [After], [Count], [Type]) if you don't use the before or after parameter the next sheet gets added before the active sheet.
|
|
#3
July 14th, 2006, 01:13 AM
|
||||
|
||||
|
what is count and type?
Can you give me an example? Thanks Last edited by teeheiman : July 14th, 2006 at 01:17 AM.
|
|
#4
July 14th, 2006, 03:34 AM
|
|||
|
|||
|
count is the number of worksheets you want to add.
not the same as worksheets.count, (which is the total worksheets in the worksheets collection). type ... well I'm not too sure about that one. The default type is a regular worksheet. I'm not that familiar with the other types of worksheets that you can specify. Maybe someone else can give you a better explanation about the worksheet types. all 4 parameters are optional, you don't need to use all of them. for instance, in your code you didn't use any.
|
|
#5
July 14th, 2006, 07:15 PM
|
||||
|
||||
|
Add Method of Sheets Class Failed
I tried this:
wrk.sheets.add(After) But Im receving this error on it: Add Method of Sheets Class Failed
|
|
#6
July 17th, 2006, 05:55 PM
|
||||
|
||||
|
Heres the correct codeing:
Code:
wrk.sheets.add ,wrk.sheets(wrk.sheets.count) as for wrk is pointing to the workbook
|
|
| Viewing: ASP Free Forums > Programming > Visual Basic Programming > Excel Application |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
