Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Protect An Excel File From Being Copied To External Device


I have a file used by our employees through our local area network. Because that file has many functions and possibilities that took us a lot of time to create, I would like to protect this file so no employee can copy it to an external device without a password. It would be great, though, if the file could be exchanged or sent among employees as logn as it remains WITHIN our internal network.

Is it technically possible to protect an Excel file in a such a way?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
External File(s) Referenced And File Links Change Based On Row Cell
I have attached the file I am working on. I am attempting to create a link to an external file based on the value of cells in column A. Then I would like to simply copy the formula down, lets say in Column B, the rows and as I do the external file reference will change depending on the value within the cell in Column A. I hope that I am making sense.

I am using Windows Vista with Excel 2007. The files will all be within the same file folder, however, there are hundereds of files so I won't be able to open them all for the indirect to work.


View Replies!   View Related
Formula To External File, Requires File To Be Open
I've got a couple of formulas in a workbook which lookup values in another workbook. When I reopen the initial workbook the formulas results come back as errors (i.e. #VALUE!) unless the workbook it is looking at is also open. This is not desirable, does anyway know how I can lookup values in another workbook which are not open? The formula which is causing me problems is as follows:

= SUMIF( 'H:Infozoom[2006-01_assy.xls]New Table1'!$F:$F,$A8,'H:Infozoom[2006-01_assy.xls]New Table1'!$AH:$AH)/60

View Replies!   View Related
Link To External File With Changing File Name
Basically I want to point my Excel sheet to a bunch of external Excel files. To get data from an specific cell (in this case, cell F10) of the external file, all I have to do is this: ...

View Replies!   View Related
Open External File
I have a userform which saves project details....

Part of the makeup of the userform is a label control (label11) which can store a filepath for an external file (word doc, PDF etc)

How can i open this external file? (the filepath is stored as the caption)


View Replies!   View Related
Extracting Value From External File
I have been trying something like this before, i could not get it fixed. Let's give it another go:

I have a number of files in a specific folder. I need to extract values from cell Sheet1!G42 out of [b]every file[b] in this folder. Problem that occurs, is that I do not know how many files are in this folder, neighter do I know what the filenames are. I need some magic to perform extraction of the desired values.

As for the files in the specified folder I have this VBA code, that suits my needs just fine:

Private Sub Workbook_Open()
Dim fileList() As String
Dim fName As String
Dim fPath As String
Dim I As Integer

This code will display all found files, including their path in column AD. (as plain text)
[b]Question that remains:[b] how am I to fix a formula In (column AE) that will extract the value found in Sheet1!G42 of each file? This must be very easy, but i can’t get this to work….

View Replies!   View Related
Copy And Paste From External File
I am trying copy and paste data from an external file. The steps I am using to get data are as follows:

1. open external file
2. copy desired data from external file (any number of rows & columns)
3. close the external file
4. paste data starting with designated cell.

The code works fine up until the part of the code where I want to paste special (I need to preserve cell formatting). Error is "PasteSpecial method of Range class failed". No sure why code doesn't work at this point.

View Replies!   View Related
Dropdown List From External File
I have created a scorecard which has 4 key drop down lists:

- Site, Coach, Team and Agent

Coach and Team populate the dropdowns dependant on which site is selected and Agent is based on which team is selected.

I have created an external file called dropdowndata.xlsx as the main source. This file has two sheets. Sheet 1 (dropdowndata) is the main Site, Coach and Team data and then Sheet 2 (GLAteam) contains the linking Team and Agent data.

The drop down lists are based on a number of named ranges and lookups.

So the issue I am having is that the drop down lists only work if I have the dropdowndata.xlsx file open on my laptop.

Is there anyway to get this to work without having to actually open the file?

View Replies!   View Related
Importing Data From External File
I have bit of code that opens an external excel file (call it the source workbook), grabs certain data, copies the data to other excel file (call it the destination workbook), and then closes. I have most of the code completed but am running into an issue with how to grab certain data from the source workbook and copying it to destination workbook.

I want the code to read the data in the source workbook line-by-line.

For each line that is read, I want to copy part of that data (value in column A and column B) to column A and column B of destination workbook only if the value in column D is equal to “Block”. For column C and column D, I want to do some form of lookup, being I want the code to look at each line of data until it finds corresponding line in which column A = “60612105” and in which column B = the value in B. Once this line is found, copy the value in C to column C of destination workbook and value in D to column D of destination workbook.

I have attached two files. First file is the import file (destination workbook) which has two tabs; (1) import tab (2) desired result. Second file is the raw data file (source workbook). Hopefully this makes since.

View Replies!   View Related
Test Whether Or Not An External File Exists?
Is there a way to test (from Excel 2002) whether a specified file (not necessarily an Excel file) exists on my computer? I could specify the complete path.

View Replies!   View Related
Set Up A File With Drop Down Selections From Column B To R Copied Down To Row 3000
I have set up a file with drop down selections from column B to R copied down to row 3000. I am sure this is why the file is slow when you make a selection. How does one speed this up?? Is it possible?? I created it using Excel 2007 saved as a 2003 version.

View Replies!   View Related
Copying Values From External Text File
I have an external file that is a table with 4 columns and about 25 rows
I would like to copy the contents of a file into a collection so I can analyze the data.

The problem is, I only know how to copy a whole line as a string and have no idea how to copy the data from the same line into different variables.
I also have no idea how to define a collection array

I will explain

say my text (data.txt) file looks like this:

AB 0.5 20 2/2/07
CD 0.2 15 2/2/07
FE 0.4 40 2/2/07
(example of input)

I would like to create a collection called trade which I have defined like this (obviosly wrongly)
Dim Trade() As Collection
Dim bs As String
Dim quant As Long
Dim price As Single
Dim calendar As String
Trade.Add (bs)
Trade.Add (quant)
Trade.Add (price)
Trade.Add (calendar)

so I would like to know how to define the collection array and how to input the data so that I don't have to insert a whole line into a string variable but can, instead break apart the line during the input stage


View Replies!   View Related
Must Open External File To Link Formula
We're using SUMIF and it won't work unless the external excel file is open.

This is the formula we're using:

View Replies!   View Related
Reading External File On Workbook Open
when i run this code under a button on a sheet it works fine. but i would like to run when the workbook opens (but i get an error message

On Error Resume Next
Application. ScreenUpdating = False
Dim wb As Workbook
Dim ws As Worksheet
'create new work order number...
Set wb = Application.Workbooks.Open("c: empex_ExternalOrderNumber.xls")
Set ws = Worksheets("NumberIncrement")
'get new WO number from numberincrement.xls
If Err.Number <> 0 Then
MsgBox Err.Description & "...help"
Else
mynewnumber = ws. Range("b1").Value
Range("g5").Value = mynewnumber
wb.Close
End If

Application.ScreenUpdating = True

View Replies!   View Related
Device I/O Error
I am seeing multiple versions of my workbook open in the project window, and it seems that every time i close and reopen a workbook, as version of it is "stored" there.


View Replies!   View Related
External File Properties In Cells. Last Saved Time
I was looking for a way to get external file froperties into a cell!

for example:

Last modified date.

this is what I allready found/tried.

View Replies!   View Related
Prompt For File On External Formula Link Entry
I want to be able to get a value from a specific cell in another workbook, the problem i have is that i want to be able to select what file the active workbook looks for from the start page of my current workbook. For example when i load the workbook i get my startup page ( sheet 1) where i will be able to select what external workbook the active workbook will be linking with, i will then link the active workbook to the value in a specific cell from the selected external workbook.
I know to link to another workbook i would use ='[filename]sheet!cell

However i want to be able pull up a 'open file dialog box' which i select a file name which will be used as 'filename' in the formula above.

View Replies!   View Related
MIDI Device Error
I have followed John Walkenbach's "Playing a MIDI File" instructions and also gleaned information from the Mr. Excel "Excel Questions" message board, but I am baffled by this error message that appears when I use Mr. Walkenbach's approach:

"The specified device is not open or is not recognized by MCI."

View Replies!   View Related
How To Manage New Year File Name Change When Linked To External Workbooks?
I have a set up a workbook which is linked to numerous other workbooks to provide a summary of information in one location.

However, when we reach April the names of all the linked worksheets will change from 'name 09 - 10' to 'name 10 - 11'.

What is the best way of handling this transition, will I have to manually change all of the links?

I have attached a much simplified example, unfortunately I cannot post the original due to sensitive data.

Apologies if the explanation isnt clear enough, let me know and I'll give further detail.

View Replies!   View Related
Automatically Update External Links To Csv File In 2007
I have an Excel 2003 workbook (named TargetWorkbook.xls) with some links to a CSV file called DataSource.csv. I use Excel 2007 to open the workbook. I checked the "Update links to other documents" option under the "When Calculating this workbook..." section on the Advanced pane of the Excel Options window. I unchecked the "Ask to update automatic links" option under the "General" section on the Advanced pane of the Excel Options window. I selected the "Don't display the alert and update links" option on the Startup Prompt dialog (accessed from the Edit Links dialog) I created a simple Auto_Open macro with the following statements:

Sub Auto_Open()
Workbooks.Open Filename:="C:ProjectsExcelTestDataSource.csv", ReadOnly:=True
Worksheets("DataSource").Activate
Workbooks("TargetWorkbook.xls").Activate
Windows("DataSource.csv").Visible = xlVeryHiidden
End Sub

The DataSource.csv file is updated daily. I want the cells of TargetWorkbook.xls to automatically update from the csv file when I open TargetWorkbooks.xls without displaying a prompt. I plan to deploy this workbook to a server and open it programatically via a Windows Service (I know, I know, Microsoft doesn't encourage this)and can't have it throwing up a user dialog.

Here is the problem: When I open TargetWorkbook.xls manually using Excel 2007, it updates the links but throws up the dialog "This workbook contains one or more links that cannot be updated...". When I click on the "Edit Links..." button, it displays the message "Warning: Open source to update values". When I click the "Check status" button for this link, it says "Source is open". How do I prevent this dialog box from poping up? I've been wrestling with this for a few
days now and can't find a solution.

View Replies!   View Related
Macro Error After Protect File
I created excel form with calculated formula and macro. Everything worked fine. To protect spreadsheet formula I protected file. But after protection when I ran file I get macro error message. Then I remove protection become work without error messgae. How to fix that problem?

View Replies!   View Related
File Attributes :: Turn Off Read Only Update And Re-Protect
I have one workbook which when a macro is run, creates a new workbook. I am trying to then get this new workbook to have both "ReadOnly" and "Hidden" status. but when I use Set attr it only picks up one.

I need to be able to turn of the readonly status and update the file and then re protect it.

View Replies!   View Related
Timer- To Write Code Which Takes A Reading Off Of A Device Every .005 Seconds
I am trying to write code which takes a reading off of a device every .005 seconds.

When I write the code as follows to take the reading every .004 seconds things work well.

PauseTime=5
Start = Timer
Oldtime=Timer
Do While Timer < Start + PauseTime
If Timer > Oldtime + .004 Then
Oldtime = Timer
Read(count) = Orbit.Networks(0).Modules(0).ReadCurrent
count = count + 1
End If
Loop
Finish = Timer

If however in the above code I only change .004 to .005 instead of taking readings every .005 seconds, I end up with readings roughly .015 seconds apart (approx)

Is there something unstable about the timer function to do this? Is there a different way to get a more stable result?

View Replies!   View Related
Merits Of VBA Protect Vs Non-Protect
I would like to protect the intellectual property of an application and have read differing opinions of the merits of protecting VBA code. What are the opinions of the experts on this forum? Are the password breaker programs so advanced that protecting code is a waste of time and only a hinderance to debugging and troubleshooting?

I am using MS Excel 2003 (11.5612) on XP.

I apologize if this topic has already been covered on this forum. I searched but did not find exactly what I was looking for. I am happy to review an old post if one exists.

View Replies!   View Related
Password Protect: Unlock The Worksheet, Autofilter It, Print It, Unfilter It, And Password Protect The Sheet Again?
I have a sheet that is password protected. I have this code attached to a command button. It will unlock the worksheet, autofilter it, print it, unfilter it, and password protect the sheet again. However it is protecting it without a password. I need to have it protected with the password so that someone will not be able to just go to tools to unprotect the sheet.

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect "rainforest"
Columns("O:O").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlAnd
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter Field:=1
Selection.AutoFilter
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub

View Replies!   View Related
Reference Not Working When Copied
I've been trying to copy and drag formulas on my spreadsheet by the references are not working. Example. I have =$B1 in cell A1. If I copy and drag that to A2 it remains =$B1. And it should change to =$B2, correct? Same as if if make the column absolute.

View Replies!   View Related
Deleting Row After Copied
Just finished amending a piece of code to enable me to search for an agents name in a list of files. This is then copied to a new worksheet (named by the name entered in the search). What I need is to cut(or delete) the row from the original sheet (sheet 1 in this case) thus leaving me the remaining list of files.
Below is how the code is setup at the moment....(just copies the row)

View Replies!   View Related
Macro On Copied Sheet
Macro on copied sheet. I have a macro using the following to bring a shape to the front:

View Replies!   View Related
Keep Formatting Of Copied Data
I'm attempting to copy some data from one spreadsheet to to a workbook in another spreadsheet. However, when I do my copy, I lose all of the formating of the intial data. Does anyone know if there a way that I can keep the the format of the original to the new spreadsheet?

View Replies!   View Related
Where Do I Paste A Copied Udf
I have copied a colorindex UDF from here and went to VBE, inserted a new module in my Personal project, pasted the UDF and expected to be able to use it in any excel file. It's not working though. I have used this particular UDF before, but on a prevous computer that is not around for me to look at.

My goal is to use =IF(colorindex(J2)=colorindex($K$1),J2,"").

View Replies!   View Related
Show Copied Formula
I have a problem in copy formulas form one cell to another, my formulas are writen in one workbook in this way... '=Dog+Cat.

The problem is that I did a looking table code (vlookup) and when it shows the formula I have #żName?.

I would like to knw if there is a way to fix this using the vlookup or I'll be obligated to use special Copypaste

View Replies!   View Related
Comments With Copied Worksheet
Using Excel 2007 I duplicated a worksheet by right clicking the worksheet tab and selecting Move or copy... with the Create a copy option. Of the six comments on the original worksheet only one appears in the duplicate.

View Replies!   View Related
Formula Down Rows When Copied Across Columns
I am looking for a formula that reads down when drug to the right.


View Replies!   View Related
Check Boxes Copied From Internet
I copied a list of data from Vendio Counters for pages in my website, which includes a check Box in their 1st column.

When copying the list, you automatically get the check box plus the data.

When I paste it into my counter tracking spreadsheet, the Check Box is also pasted into one of my columns.

The problem is that I only want 1 column which is the actual count.

I have tried everything to delete the Check boxes to no avail. I've even tried to delete the column and it won't delete.

All the other extra columns can be cleared or columns deleted

View Replies!   View Related
Formula Not Capturing Data When Copied Down
I am trying to copy a formula down a row. All I am getting is the exact same answer from the 1st cell instead of the new data.

View Replies!   View Related
Buttons Not Wanted On Copied Sheet
I am copying sheet 1 to the back of the workbook and renaming it. I am using a few command buttons on sheet 1 to trigger the copy macro, etc. I do not want these buttons on the copied sheets.

I have done a right click /Format Control/Properties and selected “Don’t move or size with cells” but the buttons are still being copied and still active for the macro. Perhaps due to sheet copy versus cell copy?

I guess I could add select and delete steps to the macro for the buttons but this seems excessive.

View Replies!   View Related
Relative Formula Copied Across Worksheets
It's been a long time since I have worked with Excell, it use to be the " $ " that made things absolute or relative.

I have this formula:

='1'!C194+C193 on sheet 1 named "1" without the " ". I need to copy it across 31 sheets. I thought that it was the " ! ", without the " ", that makes the address absolute so I took it out and then attemped to copie it to other sheets. The idea was that the formula would then read: ='2'!C194+C193 on sheet 2, and ='3'!C194+C193 on sheet 3 and so on. The formula is in the exact same location on each sheet, C194. But this won't work. I am at this time doing each formula one at a time. I have many workbooks that will use the same style of copy and paste; about 15 workbooks each with 31 sheets.

It would help if someone could explain how to save time. I do know how to use the Ctrl to select different cells and how to use Ctrl+Page UP/Down to change tabs. I just have to get the formula correct.

View Replies!   View Related
Formula Change To Allow A List To Be Copied.
I have a formula that basically copies a cell into another workbook if 'other' is entered into the column to the right. i,e cell A1 has 'other' B1 has '100', the figure in B1 is placed into another workbook cell. The results are only shown if a month is selected from a drop down on the 1st workbook with the meta data.

What I want to do is to add a command to the formula below so that it will recognise that after the first item is entered it will look for the next data item and not just repeat the first….

{=IF($L$92:$L$580="","",INDEX($L$92:$L$580,MATCH(1,(TEXT($C$92:$C$580,"mmmm yyyy")=TEXT($E$5,"mmmm yyyy"))*($L$92:$L$580<>""),0)))}

In the attached worksheet is an example of what I want (table 2) and what I'm currently getting (table1)…

View Replies!   View Related
Copied The Graphs To A Word Document
I made some graphs in Excel and then copied them to a Word document. Everything seemed fine, but when I printed the document some graphs had a white square under the numbers and some hadn't. I can't find out where I can change this so that all of them would be without that white background under the numbers:

http://www.shrani.si/f/B/8T/22dMjbNF/img0944.jpg

http://www.shrani.si/f/2Y/bv/1sSyqpAw/img0945.jpg

how can I change that so that all graphs will look the same (without that little squares behind the values).

View Replies!   View Related
Getting Address Of Range Copied To Clipboard?
get the address of the last range copied to the clipboard? I tinkered with GetFromClipboard but I would like to find a solution that is not dependent upon DataObjects if possible. Since copied cells are "marqueed" in Excel, is there a property relating to marqueed cells address?

View Replies!   View Related
Insert Copied Rows In All But 3 Sheets
One of the sheets in my workbook has 5 rows (Rows 1 - 5) that I need to copy and insert at the top of all but 3 of the other worksheets. I can specify the names of the 3 worksheets that I want to avoid changing. The other worksheets will be named differently each time I use the macro.

View Replies!   View Related
Execute Procedure Whenever A Range Is Copied
how I can have a routine execute whenever a cell range is copied in excel. Doesn't matter whether by selecting the appropriate menu item or when CTRL+C is pressed (I mean it must work anyway).

I need this cause I would like to store in a public variable the address of the range which is being copied in order to use it later for undoing purposes.

I first tried to find a way of accessing what has been copied into the clipboard but it doesn't seem possible without using external DLLs.

View Replies!   View Related
Express Copied Number As Percentage
I would like to use vba to copy a value in F31 to B5 in sheets("2") and express it as a percentage.

For example, the value in F31, 102 is copied to B5 as 102. Like to have it express as 102%.

Sheets("S1").Select
Range("F31").Select
Selection.Copy
Windows("Report.xls").Activate
Sheets("2").Select
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

View Replies!   View Related
Change Sheet Name In Formula When Copied Down
Suppose I have 100 worksheets in a workbook ( named 1,2 3 ........100) in a collecteve sheet ( I have a coulmn of 100 same formulas refering to the same cell in all the 100 worksheets)

for example :
'1'!$a$1
'2'!$a$1

if I drag the first formula Fill Down With Fill Handle then, the sheet name will be the same all the way down(1) , which is not correct , so I have to go bach and modify them manually , which is very boring and mistakes could easily happen

View Replies!   View Related
Rows Copied Into List Box
I have a spreadsheet that is controlled solely by userforms. I have a search button that finds all cells that match the query and copies the whole row into another sheet, often about 10 rows appear. What I now would like to happen is that these rows are then copied into a list box for viewing in the form apposed to in the spread sheet.

I have a sample spreadsheet to show how the data would be set out.

View Replies!   View Related
Cell References In Copied Sheets
I have created some code that copies a master workbook into a new workbook that then creates new tabs for the sheets relevant to week numbers. In the master workbook there are 8 sheets, most of which reference each other in their formulas at some point. The problem arises in the newly created workbook as the sheettab names are renamed e.g "manager" in the master will become "manager wk1", "manager wk2" and so on. All the sheets are copying over great but they the lose their references to each of the sheets within their week groups because the formulas do not change to the correct week references. I am trying to solve the problem by using the Indirect funtion to change the formulas in the master to reference the correct week number, but i feel this is not the best way?

View Replies!   View Related
Macro When Copied It To Use In Another One Gives Invalid Use Of Me Keyword
I have a macro that works fine in one workbook. I copied it to use in another one, but then I get this error message: "Invalid Use of Me Keyword".

Dim wSheet As Worksheet, strMsg As String
For Each wSheet In Worksheets
If wSheet.ProtectContents = True Then
strMsg = "All sheets protected."
Else
strMsg = "All sheets unprotected."
End If
Next wSheet
MsgBox strMsg
Unload Me
End Sub

View Replies!   View Related
Option Button Name Changes When Copied To New Worksheet
I have two option buttons (Designerbutton1 & Designerbutton2), when I copy them and paste them to a different worksheet their names change to Optionbutton# (# = next available number). Anyone know why & if I can prevent this. I cannot have a code that says Optionbutton16 should be renamed to what I want it to, because right now 16 is the next available # but what if I add an Optionbutton16 later on my own.

View Replies!   View Related
Keep Formatting Of Copied Data With Destrange
I've been able to accomplish the main point of my macro which is to copy a section of code from each file in a folder and paste it into a different workbook. My problem is that when I paste the data, I lose all of the formatting of the data. ie, color, size, alignment. I know that PasteSpecial should accomplish that, but I'm using "destrange". If there is an easy way to convert to PasteSpecial... Otherwise, any ideas on how to make it work with destrange?

If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
' Find the last row with data
rnum = LastRow(sh)

'create the destination cell address
Set destrange = sh.Cells(rnum + 2, "A")

' Copy the workbook name in Column E
sh.Cells(rnum + 1, "E").Value = MyPath & MyFiles(Fnum)

'Get the cell values and copy it in the destrange
'Paste the data in the previously established "destrange"
GetData MyPath & MyFiles(Fnum), "SDRL Status Rpt", "A4:G6", destrange, False, False
Next
End If

View Replies!   View Related
Reference To Copied Or Cut Range
when I cut or copy a range - and then select a different area - how do I reference to the original cut or copied selection, i.e. the "marching ants" range?

View Replies!   View Related
Add Copied Worksheet Name To Cell
original thread 2. Originally Posted by Dave Hawley

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wSheet As Worksheet, wSheet2 As Worksheet
Dim strNum As String, strNum2 As String
If Target(1, 1).Address = "$J$5" Then
If Target = vbNullString Then Exit Sub
If IsNumeric(Target) Then
strNum = Target + 1
strNum2 = Target
On Error Resume Next
Set wSheet = Worksheets(strNum)
Set wSheet2 = Worksheets(strNum2)
On Error Goto 0
If Not wSheet Is Nothing Then
Application.EnableEvents = False
Application.Undo......................

What I would like to do is after the new sheet is created it would make cell j5 on the newly created sheet the same name as the sheet itself. I've tried to do that but the above code tries to activate. Is there a way to have the above code only work on the very first sheet and for the cell naming code to work on only the created sheets

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved