[RESOLVED] excell questions(help)
i have a question regarding excell and i have read about si_the_geeks tutorial about excell:
1. how can you make excell.book as a read only file? (with password)
2. how can you make it as an autoformat? i have made some error and made my computer crash several times... thats why i needed your help... :cry:
Code:
Private Sub CmdPrint_Click()
Dim iRow As Long
Dim iCol As Long
Dim xls As Excel.Application
Dim newcell As String
Set xls = New Excel.Application
xls.Workbooks.Open (App.Path & "\book1")
DoEvents
xls.Visible = True
For iRow = 0 To MSFlexGrid1.Rows - 1
For iCol = 0 To MSFlexGrid1.Cols - 1
MSFlexGrid1.Col = iCol
MSFlexGrid1.Row = iRow
newcell = Chr(iCol + 65) & iRow + 1
xls.Worksheets("sheet1").Range(newcell).Value = MSFlexGrid1.Text
Next
Next
End Sub
Re: excell questions(help)
Quote:
xls.Workbooks.Open (App.Path & "\book1")
unless you saved a workbook as "book1" without extension this will cause a problem, and will anyway as a new excel.application will be called book1, you can only open an existing excel file with workbooks.open
to save the workbook as readonly with password use
xls.activeworkbook.saveas, or better, set a workbook object to work with
Quote:
SaveAs Method
Saves changes to the sheet (Syntax 1) or workbook (Syntax 2) in a different file.
Syntax 1
expression.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodePage, TextVisualLayout)
Syntax 2
expression.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodePage, TextVisualLayout)
expression Required. An expression that returns a Chart or Worksheet object (Syntax 1) or a Workbook object (Syntax 2).
Filename Optional Variant. A string that indicates the name of the file to be saved. You can include a full path; if you don’t, Microsoft Excel saves the file in the current folder.
FileFormat Optional Variant. The file format to use when you save the file. For a list of valid choices, see the FileFormat property. For an existing file, the default format is the last file format specified; for a new file, the default is the format of the version of Excel being used.
Password Optional Variant. A case-sensitive string (no more than 15 characters) that indicates the protection password to be given to the file.
WriteResPassword Optional Variant. A string that indicates the write-reservation password for this file. If a file is saved with the password and the password isn’t supplied when the file is opened, the file is opened as read-only.
ReadOnlyRecommended Optional Variant. True to display a message when the file is opened, recommending that the file be opened as read-only.
CreateBackup Optional Variant. True to create a backup file.
AccessMode Optional Variant. The workbook access mode. Can be one of the following XlSaveAsAccessMode constants: xlShared (shared list), xlExclusive (exclusive mode), or xlNoChange (don’t change the access mode). If this argument is omitted, the access mode isn’t changed. This argument is ignored if you save a shared list without changing the file name. To change the access mode, use the ExclusiveAccess method.
ConflictResolution Optional Variant. Specifies the way change conflicts are resolved if the workbook is a shared list. Can be one of the following XlSaveConflictResolution constants: xlUserResolution (display the conflict-resolution dialog box), xlLocalSessionChanges (automatically accept the local user’s changes), or xlOtherSessionChanges (accept other changes instead of the local user’s changes). If this argument is omitted, the conflict-resolution dialog box is displayed.
AddToMru Optional Variant. True to add this workbook to the list of recently used files. The default value is False.
TextCodePage Optional Variant. Not used in U.S. English Microsoft Excel.
TextVisualLayout Optional Variant. Not used in U.S. English Microsoft Excel
you can also use SetAttr, to set readonly on the file, but only after the workbook is closed
do not set the excel application to visible before loading your grid as it will slow the code as it has to keep updating the screen, show to the user after filling the cells
Re: excell questions(help)
wow, very informative, with complete set of strings... good job.... cheers!!!
Re: excell questions(help)
can someone tell me how to deactivate the copying of cell from another workbook, tnx... :)
Re: excell questions(help)
Moved To Office Development
Re: excell questions(help)
Quote:
Originally Posted by ungas023
can someone tell me how to deactivate the copying of cell from another workbook, tnx... :)
Do you mean
"Application.CutCopyMode = False"
?
Re: excell questions(help)
yep, but it never worked... :(
Re: excell questions(help)
the only way i can think of is to keep monitoring the clipboard and remove anything copied in if it equals the active cell /range
Re: excell questions(help)
hmmm.... i will try that.... tnx for all the help... cheers... ^_^
Re: excell questions(help)
Why don't you clear the clipboard like West mentioned. Check this link out to clear the CB.
http://www.vbforums.com/showthread.php?t=449922
Re: excell questions(help)
hi, tnx for all of your suggestions, but the problem with this is i can always copy the value of the cell inside it, i am thinking of if he clicks a cell, then the value or picture inside a particular cell it will automatically hide then when he unclick it, it will be visible again..... that way it will be safe to say that he wont get any data from that particular workbook... i am reading alot about excell now.... ^_^
Re: excell questions(help)
no, it is pretty well impossible to completely stop someone from copying data from a spreadsheet once it is open, you can do several things to make it more difficult, but i doubt if you can stop someone determined
Quote:
value or picture inside a particular cell it will automatically hide then when he unclick it, it will be visible again
you can try this in the code window for your sheet, or you can modify for thisworkbook to cover all sheets
vb Code:
Dim rngarr As Variant, addr As String
Private Sub Worksheet_Activate()
addr = ActiveCell.Address
rngarr = Range(addr)
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range(addr).Value = rngarr
addr = Target.Address
rngarr = Range(addr)
Range(addr).Value = ""
End Sub
you would have to implement something else for pictures
Re: excell questions(help)
lol, yeah! nothing beats a determined person, i've tried almost everything and the problem keeps piling up, dont know what to do now..... :D
and btw, they can cheat into almost anything, even a report with .rpt and access or data report can be cheated to0..... oh boy..... am i making myself paranoid or what? :p
i will rate this forum as a half resolved.... evrything in this thread is very useful except the copying of workbooks/worksheets....
Re: excell questions(help)
you can disable edit menu items (cut and copy), but i am not sure if that will disable the shortcut keys (ctrl c) as well
Re: excell questions(help)
hrm, but thats another problem, i need to know this one by one, perhaps i will post it here when i got the code for disabling the ctrl+c.....
Re: excell questions(help)
i can disable the menu items copy in both edit and rightclick menus, but ctrl c still works
may be able trap that in keydown event
there are still other menu items to move or copy worksheet, send by email etc etc
Re: excell questions(help)
Seems like my first post went unnoticed... but still here is something which you are looking for... (Disabling Copy/Paste. Also disabling the shortcuts)
Code:
'-- Disable Copy Paste
Sub CopyPasteDisable()
EnableControl 21, False
EnableControl 19, False
EnableControl 22, False
EnableControl 755, False
Application.OnKey "^c", ""
Application.OnKey "^v", ""
Application.OnKey "+{DEL}", ""
Application.OnKey "+{INSERT}", ""
Application.CellDragAndDrop = False
End Sub
'Enable Copy Paste
Sub CopyPasteEnable()
EnableControl 21, True
EnableControl 19, True
EnableControl 22, True
EnableControl 755, True
Application.OnKey "^c"
Application.OnKey "^v"
Application.OnKey "+{DEL}"
Application.OnKey "+{INSERT}"
Application.CellDragAndDrop = True
End Sub
Sub EnableControl(ByVal lngCommandId As Long, _
ByVal blnEnabled As Boolean, Optional ByVal blnChangeCaption As Boolean)
On Error Resume Next
Dim ctlControls As CommandBarControls
Dim ctlControl As CommandBarControl
Dim strCaption As String ' Get all matching command controls.
Set ctlControls = CommandBars.FindControls(ID:=lngCommandId)
If Not ctlControls Is Nothing Then ' Get caption.
strCaption = ctlControls.Item(1).Caption
If blnChangeCaption = True Then
If blnEnabled = True Then
If Right$(strCaption, 9) = " Disabled" Then
strCaption = Left$(strCaption, Len(strCaption) - 9)
End If
Else
If Not Right$(strCaption, 9) = " Disabled" Then
strCaption = strCaption & " Disabled"
End If
End If
End If
For Each ctlControl In ctlControls
ctlControl.Enabled = blnEnabled
ctlControl.Caption = strCaption
Next
End If
End Sub
Re: excell questions(help)
Re: excell questions(help)
lol, nice.... but i want it to discover myself and will ask you guys if i am in a deadlock in my coding, tnx anyway, i will give you a rating too.... :D