Calling VBS Gurus! Need to disble copy and paste Excel.

[#1]

Hey Guys,

I have the following macro that exports to Excel. I have protected the Doc with a password, but I need to Disable all cut, copy and paste functions.

Any Ideas?

Sub TEST

DIM vPreviousDay
vPreviousDay = date -1
DIM NewFileName
NewFileName ="C:\Temp\TEST.xlsx"   

set XLApp = CreateObject("Excel.Application")    

XLApp.Visible = true            

set XLDoc = XLApp.Workbooks.Add

set XLSheet = XLDoc.Worksheets("Sheet1")        

XLDoc.Sheets(1).activate
Const xlCenter = -4108
set obj = ActiveDocument.GetSheetObject("CH07")       
'Step 3.1 Copy to clipboard and paste to Sheet
obj.CopyTableToClipboard true
XLDoc.Sheets("Sheet1").Paste()
XLDoc.WorkSheets("Sheet1").Cells.select
XLDoc.WorkSheets("Sheet1").Cells.EntireRow.RowHeight = 12.75
XLDoc.WorkSheets("Sheet1").Cells.EntireColumn.ColumnWidth = 12.75
XLDoc.WorkSheets("Sheet1").Cells.EntireRow.AutoFit
XLDoc.WorkSheets("Sheet1").Cells.EntireRow.Borders.ColorIndex = 0   
XLDoc.Worksheets("Sheet1").Range("N:N").NumberFormatLocal="##0.00"
XLDoc.Worksheets("Sheet1").Protect("PASSWORD")      

XLSheet.Name = "TEST"

XLDoc.SaveAs NewFileName
XLApp.Quit

ActiveDocument.Save 

End Sub

[marcus_sommer]

Disable from such functions must directly included in this excel-file in worksheet-object with code like this:

Private Sub Worksheet_Activate()

Application.CellDragAndDrop = False 'Ziehen mit der Maus

Application.OnKey "^x", "" 'Ausschneiden mit "Strg + X"

On Error Resume Next

Application.CommandBars(1).Controls("&Bearbeiten").Controls("A&usschneiden").Enabled = False 'Menübefehl deaktivieren

Application.CommandBars(30).Controls("Ausschneiden").Enabled = False ''Kontext-Menübefehl deaktivieren

Application.CommandBars(33).Controls("Ausschneiden").Enabled = False ''Kontext-Menübefehl deaktivieren

'Application.CommandBars("Cell").Controls("Ausschneiden").Enabled = False ''Kontext-Menübefehl deaktivieren

On Error GoTo 0

End Sub

Private Sub Worksheet_Deactivate()

Application.CellDragAndDrop = True

Application.OnKey "^x"

On Error Resume Next

Application.CommandBars(1).Controls("&Bearbeiten").Controls("A&usschneiden").Enabled = True

Application.CommandBars(30).Controls("Ausschneiden").Enabled = True

Application.CommandBars(33).Controls("Ausschneiden").Enabled = True

'Application.CommandBars("Cell").Controls("Ausschneiden").Enabled = True

On Error GoTo 0

End Sub

I suggest you takes always a master-file with appropriate code to open+rename+paste+save your data in excel.

- Marcus