Copy To XL5

I have two selects but I want both of them in their layout in one spread sheet. is that possible?

copy to "c:\MyFile1.xls" TYPE Xl5


Here is the lay out of what they look like: Lets say these are two different cursors c1 and c2 and I wand both copy to xls on the same sheet so I can then automate for graphing later.

itemcode	description	wk4	wk3	wk2	wk1
IC101	PRODUCT:DAMAGE	0	0	0	1
IC102	IC102	         0	0	1	0
IC103	IC103	         0	0	1	0
IC104	IC104	         0	0	0	1
IC201	IC201	         0	0	0	2
IC206	IC206	         0	0	0	1
IC303	IC303	         0	0	0	1

Open in new window

jaymz69Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
pcelbaConnect With a Mentor Commented:
So, you know how many records were in each cursor and you may simply open the XLS file after its creation and insert some separator line(s) between data from two cursors in one sheet. This should be very easy achievable via automation.

You could also insert the separator record directly into the cursor and export data with this record.

And without the separator you may use the first record in the unioned cursor to store some data about the number of records in each input cursor (see code).

Or do you have some specific requirements for two cursors separation on one Excel sheet?

SELECT SPACE(LEN(itemcode)) AS itemcode, SPACE(LEN(description)) AS description, ;
       RECCOUNT("Cursor1") AS Wk4, RECCOUNT("Cursor2") AS Wk3, ;
       00000000 AS Wk2, 00000000 AS Wk1 ;
  FROM Cursor1 ;
 WHERE RECNO() = 1 ;
UNION ALL ;
SELECT * FROM Cursor1 ;
UNION ALL ;
SELECT * FROM Cursor2 ;
INTO CURSOR cOutput

copy to "c:\MyFile1.xls" TYPE Xl5

Open in new window

0
 
pcelbaCommented:
The easiest solution probably is to create a union from your two cursors and export the result (it should not be a problem when your two cursors have same structure):

SELECT * FROM Cursor1 ;
UNION ALL ;
SELECT * FROM Cursor2 ;
INTO CURSOR cOutput

copy to "c:\MyFile1.xls" TYPE Xl5

Open in new window

0
 
jaymz69Author Commented:
But I want to keep them seperate because I will need to create two graphs from them.
They are two different entities
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
jrbbldrConnect With a Mentor Commented:
You can write each of the query results to its own separate Excel file and then merge them into a single Excel file with each on its own worksheet within the workbook.

As I have said in your previous question post and in your other numerous posts on related subjects relating to VFP Automation....

Do the work manually in Excel and record the actions as a Macro.   Then examine the Macro to find out what you need to do and, in most cases, how to do it with VFP Automation.

Good Luck
0
 
tusharkanvindeConnect With a Mentor Commented:
I have this code from Cetin Basoz, but the source has to be tables and not cursors as it uses VFPOLEDB . You will also have to install OLEDB driver for VFP

* Cetin Basoz
#Define xlTop -4160
Local oExcel
oExcel = Createobject("Excel.Application")
With oExcel
  .WorkBooks.Add
  .Visible = .T.


  VFP2Excel(_samples+'data\testdata.dbc',;
    'select emp_id,first_name,last_name,CAST(CHRTRAN(notes,CHR(13),"") as M) as Notes from employee',;
    .ActiveWorkBook.ActiveSheet.Range('A1'))

Endwith




Function VFP2Excel
  Lparameters tcDataSource, tcSQL, toRange

* tcDataSource is the fullpath to the DBC
* tcSQL is the SQL Statement to run on the DBC to get the records to be transferred to excel
* toRange is the place in the excel file where the data is to be copied

  Local loConn As AdoDB.Connection, ;
    loRS As AdoDB.Recordset,;
    ix
  loConn = Createobject("Adodb.connection")
  loConn.ConnectionString = "Provider=VFPOLEDB;Data Source="+m.tcDataSource
  loConn.Open()
  loRS = loConn.Execute(m.tcSQL)

  For ix=1 To loRS.Fields.Count
    toRange.Offset(0,m.ix-1).Value = Proper(loRS.Fields(m.ix-1).Name)
    toRange.Offset(0,m.ix-1).Font.Bold = .T.
  Endfor
  toRange.Offset(1,0).CopyFromRecordSet( loRS )
  loRS.Close
  loConn.Close
Endfunc
0
 
tusharkanvindeCommented:
You could also use _vfp.datatoclip to copy the data to the clipboard and then use automation to paste it in excel. This will not work for memo fields.
0
 
jaymz69Author Commented:
Each cursor has its count on codes
code 111 count=5  between dates
code 921 count=3 between dates


the other cursor has
code 111T (for time) had 45 minutes between dates
code 921T  had 15 minutes between dates

       

I then would build two seperate cluster bar graphs based of the data
(of course  there are more than one code...)
the two graphs are seperate so since time could be a large number while the same code could only have a count of 1


0
 
jaymz69Author Commented:
I have it all being made into seperate .xls
in seprate Emails
can even do the sheets in one workbook

To me it would be better to have it all in one view (one sheet), like a dashboard...

Thanks for all your input, I really do enjoy reading all angles from everyone...
0
 
jaymz69Author Commented:
This is just an example of what I invision as one sheet with all three on it

this is just sample numbers

I do get these on seperate .xls now no problem. Just want it all on one sheet, one email, one view...


EX-GRAPH-CODES.xls
0
 
jrbbldrCommented:
"I do get these on seperate .xls now no problem. Just want it all on one sheet, one email, one view"

Great.
Now do as I have suggested above and in other of your postings....

Do the work manually in Excel and record the actions as a Macro.   Then examine the Macro to find out what you need to do and, in most cases, how to do it with VFP Automation.

Then write the VFP Excel Automation code with the Macro code as a model.

You might also want to consider purchasing the book:  Microsoft Office Automation with Visual FoxPro  ( http://www.hentzenwerke.com/catalog/autofox.htm )

Good Luck



0
 
jaymz69Author Commented:
I found this that was pretty darn near close to what I was trying to ask but better said by this person

http://www.foxite.com/archives/export-to-multipage-excel-workbook-0000282478.htm

Then of course tweaked it and debugged it to work for my scenario and other additions

I still want all three on one sheet but this is another step closer

Thanks all!
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.