Solved

Copy To XL5

Posted on 2010-11-17
11
1,172 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:jaymz69
  • 5
  • 2
  • 2
  • +1
11 Comments
 
LVL 41

Expert Comment

by:pcelba
ID: 34160672
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
 

Author Comment

by:jaymz69
ID: 34160694
But I want to keep them seperate because I will need to create two graphs from them.
They are two different entities
0
 
LVL 41

Accepted Solution

by:
pcelba earned 167 total points
ID: 34160795
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
 
LVL 12

Assisted Solution

by:jrbbldr
jrbbldr earned 166 total points
ID: 34160823
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
 
LVL 14

Assisted Solution

by:tusharkanvinde
tusharkanvinde earned 167 total points
ID: 34161564
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 14

Expert Comment

by:tusharkanvinde
ID: 34161576
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
 

Author Comment

by:jaymz69
ID: 34166056
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
 

Author Comment

by:jaymz69
ID: 34166093
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
 

Author Comment

by:jaymz69
ID: 34166199
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
 
LVL 12

Expert Comment

by:jrbbldr
ID: 34166931
"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
 

Author Comment

by:jaymz69
ID: 34175958
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now