mrfitness


I used a program to copy results of a query into an excel sheet. Now I would like to hide two sheets that I renamed in the program - 'High' and 'Med'. I would also like to protect the book.
How can I do this Is this even possible Some sample coding below:
esheet=1
loExcelTemplate.sheets[esheet].select
oActiveSheet = loExcelTemplate.ActiveSheet
oActiveSheet.name = "High" &&RENAME CURRENT WORKSHEET

loExcelTemplate.Columns("A:Z").EntireColumn.AutoFit


loExcelTemplate.ActiveWorkbook.Save
loExcelTemplate.Visible = .F.
loExcelTemplate.Application.Quit
Release loExcelTemplate



Re: Using VFP6 to hide sheets and protect book in excel

Naomi Nosonovsky


Do you mean you want to set password for the WorkBook If yes, check the function I posted. You simply set password like this

IF NOT EMPTY(m.tcPassword)
loExcel.ActiveWorkbook.PASSWORD = m.tcPassword && Works in Excel 2003
ENDIF

I'm not sure how to hide sheets. Did you try to do it in Excel and record macro, if this possible





Re: Using VFP6 to hide sheets and protect book in excel

mrfitness

I can hide sheets using a macro in excel,but I will be creating MANY excel files with this program and I would rather have FP do it than opening each one up and running a macro to hide 2 sheets...
Below is a sample that I have in an excel macro to hide the sheet called "Email"

Sheets("Email").Visible = False





Re: Using VFP6 to hide sheets and protect book in excel

mrfitness

How and where do I add the function to my code (I am not accustomed to adding functions)
Below is the majority of the code...can you please reply with added function Thanks!

clear all
close tables
clear
set talk on
SET EXACT ON
SET SAFETY OFF

sDir = SYS(5)+SYS(2003)+ "\"
nDir = SYS(5)+SYS(2003)+ "\impact\"


sele 1
use foxytest
select contractno from foxytest group by 1 into table contract
close all

sele 1
use foxytest

sele 2
use contract

scan
a = alltrim(contractno)
b = a + ".txt"

lcFileName = (nDir + a + ".XLS")
titlename = alltrim(contractno) + ".txt"

sele 1


select contractno,prdnam2010,prdcodepcl,sum(total_rev) as tot_rev, sum(total_base) as tot_base, sum(list_2010) as totlst2010, count(*) as ships ,;
from foxytest group by 1,2,3 where contractno = a into cursor c2

Copy File "C:\Documents and Settings\NPicchiello\Desktop\Book1.xls" To (lcFileName)

loExcelTemplate = Createobject("Excel.Application")

esheet = 2
nRow = 0

With loExcelTemplate

.workbooks.Open(lcFileName)


.Visible=.F.

&&FILLING IN TITLES FOR EACH FIELD/ADDITIONAL WORKSHEET
.sheets[esheet].cells(1,1).Value = "Contractno" &&COLUMN HEADER
.sheets[esheet].cells(1,1).font.fontStyle = 'Bold'
.sheets[esheet].cells(1,2).Value = "ProdName2010" &&COLUMN HEADER
.sheets[esheet].cells(1,2).font.fontStyle = 'Bold'
.sheets[esheet].cells(1,3).Value = "ProdCodePCL" &&COLUMN HEADER
.sheets[esheet].cells(1,3).font.fontStyle = 'Bold'
.sheets[esheet].cells(1,4).Value = "Total_rev" &&COLUMN HEADER
.sheets[esheet].cells(1,4).font.fontStyle = 'Bold'
.sheets[esheet].cells(1,5).Value = "Total_base" &&COLUMN HEADER
.sheets[esheet].cells(1,5).font.fontStyle = 'Bold'
.sheets[esheet].cells(1,6).Value = "Total_lst2010" &&COLUMN HEADER
.sheets[esheet].cells(1,6).font.fontStyle = 'Bold'
.sheets[esheet].cells(1,7).Value = "Ships" &&COLUMN HEADER
.sheets[esheet].cells(1,7).font.fontStyle = 'Bold'

ln2Col = 2
SCAN
&&FILL IN VALUES OF LENGTHS FOR THE FIELD IN THE CURSOR
.sheets[esheet].cells(ln2Col,1).Value = alltrim("'"+Alltrim(c2.contractno))
.sheets[esheet].cells(ln2Col,2).Value = alltrim(c2.prdnam2010)
.sheets[esheet].cells(ln2Col,3).Value = alltrim(c2.prdcodepcl)
.sheets[esheet].cells(ln2Col,4).Value = alltrim(str(c2.tot_rev,10,2))
.sheets[esheet].cells(ln2Col,5).Value = Alltrim(str(c2.tot_base,10,2))
.sheets[esheet].cells(ln2Col,6).Value = Alltrim(str(c2.totlst2010,10,2))
.sheets[esheet].cells(ln2Col,7).Value = Alltrim(str(c2.ships)) ln2Col = ln2Col+ 1

Endscan

Endwith

loExcelTemplate.sheets[esheet].select
oActiveSheet = loExcelTemplate.ActiveSheet
oActiveSheet.name = "Med" &&RENAME CURRENT WORKSHEET

Select 1

loExcelTemplate.ActiveWorkbook.Save
loExcelTemplate.Visible = .F.
loExcelTemplate.Application.Quit
Release loExcelTemplate

select contractno,sum(total_rev) as tot_rev, sum(total_base) as tot_base,sum(list_2010) as totlst2010, count(*) as ships, ;
from foxytest group by 1 where contractno = a into cursor c1

loExcelTemplate = Createobject("Excel.Application")
esheet = 1
nRow = 0

With loExcelTemplate

.workbooks.Open(lcFileName)

.Visible=.F.

&&FILLING IN TITLES FOR EACH FIELD/ADDITIONAL WORKSHEET
.sheets[esheet].cells(1,1).Value = "Contractno" &&COLUMN HEADER
.sheets[esheet].cells(1,1).font.fontStyle = 'Bold'
.sheets[esheet].cells(1,2).Value = "Total_rev" &&COLUMN HEADER
.sheets[esheet].cells(1,2).font.fontStyle = 'Bold'
.sheets[esheet].cells(1,3).Value = "Total_base" &&COLUMN HEADER
.sheets[esheet].cells(1,3).font.fontStyle = 'Bold'
.sheets[esheet].cells(1,4).Value = "Total_lst2010" &&COLUMN HEADER
.sheets[esheet].cells(1,4).font.fontStyle = 'Bold'
.sheets[esheet].cells(1,5).Value = "Ships" &&COLUMN HEADER
.sheets[esheet].cells(1,5).font.fontStyle = 'Bold'


ln2Col = 2

&&FILL IN VALUES OF LENGTHS FOR THE FIELD IN THE CURSOR
.sheets[esheet].cells(ln2Col,1).Value = alltrim("'"+Alltrim(c1.contractno))
.sheets[esheet].cells(ln2Col,2).Value = alltrim(str(c1.tot_rev,10,2))
.sheets[esheet].cells(ln2Col,3).Value = Alltrim(str(c1.tot_base,10,2))
.sheets[esheet].cells(ln2Col,4).Value = Alltrim(str(c1.totlst2010,10,2))
.sheets[esheet].cells(ln2Col,5).Value = Alltrim(str(c1.ships,10,0))


Endwith


loExcelTemplate.sheets[esheet].select
oActiveSheet = loExcelTemplate.ActiveSheet
oActiveSheet.name = "High" &&RENAME CURRENT WORKSHEET

loExcelTemplate.Columns("A:Z").EntireColumn.AutoFit


loExcelTemplate.ActiveWorkbook.Save
loExcelTemplate.Visible = .F.
loExcelTemplate.Application.Quit
Release loExcelTemplate

sele 2
endscan




clear all
close all
set talk on
set status bar on







Re: Using VFP6 to hide sheets and protect book in excel

mrfitness

I used this code to hide the sheets:

oActiveSheet.Visible = .F.




Re: Using VFP6 to hide sheets and protect book in excel

Naomi Nosonovsky

I'm not sure what you're asking. Are you still having problems I would create one generic function DbfToExcel as I showed and just call it after you created your cursor. You can put all functions into one big procedure file and then in the main program use this command

SET PROCEDURE TO myMainProcedure additive

or you can have each function in its own prg with the same name.

Also, as I mentioned in your other thread, NEVER specify numbers with the SELECT command. SELECT 0 will open the next available workarea.





Re: Using VFP6 to hide sheets and protect book in excel

CetinBasoz

You can protect all or part of an excel workbook. In the same manner you can hide different things (and they require different properties be set - ie: for sheets visible = true/false, for columns hidden = true/false).

This sample protects the whole workbook and hides sheet2:

* Test data for Sheet1

local lcTemp,lcTabDelimitedData

lcTemp = forcepath(sys(2015)+'.tmp',sys(2023))

select Cust_id,Company,Country,Contact ;

from (_samples+'data\customer') ;

into cursor crsCustomer readwrite

copy to (m.lcTemp) type delimited with "" with tab

lcTabDelimitedData = filetostr(m.lcTemp)

erase (m.lcTemp)

_cliptext = 'Customer ID' + chr(9) +;

'Company' + chr(9) +;

'Contact Name'+chr(13)+chr(10)+;

m.lcTabDelimitedData

* Test data for Sheet1


oExcel =
createobject("Excel.Application")

with oExcel

.WorkBooks.add

with .ActiveWorkBook

.WorkSheets(1).range('A1').PasteSpecial() && Paste Test data

endwith

.ActiveWorkbook.ActiveSheet.EnableSelection = 1

.ActiveWorkbook.ActiveSheet.protect("mypassword",.t. ,.t., .t.)

.Activeworkbook.Sheets(2).Visible = .f.

.ActiveWorkbook.saveas("c:\temp\myProtected.xls")

.ActiveWorkBook.save

.quit

endwith

Follwoing sample only protects a single column:

Use orders

lcXLS = Sys(5)+Curdir()+'orders.xls'

Copy To (m.lcXLS) Type Xls

lcLastColumn = Chr(Asc('A')-1+Fcount()) && Last col not locked

oExcel = Createobject('Excel.application')

With oExcel

.Workbooks.Open(lcXLS)

With .ActiveWorkbook.ActiveSheet

.UsedRange.Locked = .T.

.Range(m.lcLastColumn+':'+m.lcLastColumn).Locked = .F.

.Protect('mypassword')

Endwith

.Visible = .T.

Endwith





Re: Using VFP6 to hide sheets and protect book in excel

Cindy Winegarden

mrfitness wrote:
I can hide sheets using a macro in excel,but I will be creating MANY excel files with this program and I would rather have FP do it than opening each one up and running a macro to hide 2 sheets...

Hi!

What Naomi meant was to record a macro in Excel and then open the VBA editor to see what the macro code was. Then, it's pretty easy to translate the VBA code into VFP.