Macro Protection Using Information From External File?
Jun 21, 2014
I was wondering if it is possible to protect a macro to be used only if a *.txt (for example) file wit some certain text is available. like a key to run hidden in a text. The file is situated in a company network, and i want to protect it from taking the file out of the company. I can do it to check for presence of a file - if the file is not available - the macro exits. but if someone break the password that is protecting the code will see the blocking point immediately. this is why i want to use a key....
I have file name "WM" from where I want to run macro and want that It open a File let say "test" & do the rest of the functions. like if I have following code how can it possible.
"TML" sheet is on file "WM" and "MD" sheet is on file "test".
Code:
Sub Copy_on_backup_file() Workbooks.Open Filename:="C:UsersBinDesktop est.xls" '*** Select the destination cell With Sheets("TML") LR = .Range("A" & Rows.Count).End(xlUp).Row Range(.Cells(3, 1), .Cells(LR, 3)).Copy Sheets("MD").Range("B" & Rows.Count).End(xlUp).Offset(1) End With
I have a sheet used to process sales orders - about 500 orders per month x ~1.5Mb each. I want to save them as smaller files.
The sheets to save are full of vlookups linked to external sheets and tabs, other formulas, and macros - most of which do not need saving when the sales order is filed. There are lots of merged cells too.
I must save: 1. Cell values 2. Cell formats incl merged cells, borders, colour, font etc. 3. The row and column sizes 4. Print set ups - print area, margins, page setup, header footer etc - (Everything needed to reprint to same as original)
I think I need a "File save as" style Macro which opens a dialogue box for the user to nominate the destination folder (& allows the user to browse for it), and a new file name.
The original file name is "Job Sheet - Omlaw.xls" The tab/sheet to save is "Front Sheet" (There are two other tabs - neither of which need saving.)
All cells to be changed to "locked".
The sheet must be password protected in Excel. User to enter it - or not.
The saved file should be "write protected" if possible?
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.
I have a file that gives some statistical data to my co-workers every 15 minutes. A common question I get is "How does that compare to last week?" Then I have to open the file from 7 days ago, find the data from the same time interval, and subtract it from this week's number in my head. I'd like to have excel do this for me.
I know how to get data from an external file. The problem is, these files are named with a date on the end of the file name. So tomorrow, the static formula won't work anymore (or rather, it will give data for a file from 8 days ago, instead of 7). I'd like to excel to use today's date, find the file from 7 days ago, and get the data from that file to compare to the current file.
Here's what I've done so far:
Code: ThisDate = Range("C1").Value 'the cell with todays date in it ThisDateName = Format$(ThisDate, "yyyy-mm-dd") 'now formatted the way I need it ThisDate7 = Range("G1").Value 'the cell with the date 7 days ago ThisDateName7 = Format$(ThisDate, "yyyy-mm-dd") 'formatted correctly ThisDate14 = Range("G2").Value 'the cell with the date 14 days ago ThisDateName14 = Format$(ThisDate, "yyyy-mm-dd") 'you know, in case of a holiday 7 days ago
I have no clue what to do next. I want Excel to: Use "ThisDateName7" to find the file with the name "pph_tracker_[ThisDateName7].xlsm"Get data from a cell in that file (say, C15)Subtract it from the data in the same cell (C15) in today's file (this week - last week)Give me the result in today's file (say in cell C20)Do that again for cells D15, E15, and so on (result in D20, E20, etc)
I assume I can figure out the rest from there. Can I use the variable names in an actual formula in cell C20? Something like:
=C15 - '[pph_tracker_{ThisDateName7}.xlsm]Sheet1'!C15 or even =C15 - '[pph_tracker_{The Date in Cell $G$1}.xlsm]Sheet1'!C15 which would require no macros at all!
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:
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: ...
I need to protect excel file. I have search vba codes and tried to combine : 1. Must enable macro on open 2. Input user id and password 3. Disable cut, copy, paste and print but not work. user : test password : 1 Details as per attachment
I have a command that opens up another workbook. This workbook is sometimes open on another computer, so that the book opens up on my computer in write-protected mode (shows in the top of the window). Usually, when opening other books through the Excel functionality, a message box appears if the file is open and warns that the file is in use by another user. However, with the code I have at the moment, this is not the case when I try to open with my command. See code below:
Private Sub cmdOpenBook_Click() 'Open book ChDir "I:DevelopmentSpecialUtvikling" Workbooks.Open Filename:= _ "I:DevelopmentSpecialUtviklingJointApp.xls" End Sub
Is there any way of displaying a msgbox warning when opening another file through VBA?
I would like to automatically update a 'yearly' database file with info from a file that is changed on a daily basis.
The daily file that i use has info like date, truck number, delivery stops, weight.
the database file has the similar headings.
at the end of each day this daily file is saved. I would like to have the info that is entered into the daily file automatically plugged into the yearly database file into the next available group of cells with respect to the salesperson.
This is kind of a generalization but i'm hoping to just get pointed in the right direction. If something like this involves vba then it will be beyond my ability and i'll have to do it manually, which is fine
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….
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.
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.
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.
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?
I am working on a research project of which details cannot be disclosed. I basically have many worksheets which each contain 3D positional data and I am plotting overlaid graphs using multiple worksheets. I have gotten them to work where I manually enter the names of the worksheets, but I am trying to use the CELL("filename") command to get the current worksheet's name, then using a substitute command to change certain fields, then I would like to use the string name created by those functions to call a global variable in another worksheet which defines a range. I will try to give an example.
Say I have 3 worksheets for arbitrary measurements: 1) filename 1mm 2) filename 2mm 3) filename 3mm
I would like to graph all 3 sets of data in worksheet "filename 1mm", using the fact that everything in the filenames are the same except for the 1/2/3mm part. I have a template of which I will be copy/pasting data from numerous data sets and then each worksheet has defined names "X_vals" and "Y_vals" which give me dynamic ranges for the data I wish to plot from that worksheet.
When I type the names manually--i.e. " 'filename 2mm'!X_vals" everything works fine, but when I try to create the exact same string dynamically using indirect/substitute/left/right etc, it does not let me do it. I am able to create a dynamic string with a range, such as " 'filename 2mm'!C10:C100" but then when I change it to " 'filename 2mm!X_vals" it just gives me #REF!.
I guess my question is just is there any way to use the indirect function to create a string name dynamically which references a defined name in another worksheet? If not possible with just simple excel functions, is there a way to do this with a macro? I am not exactly familiar with VBA in excel, though I know how to run macros.
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
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
I've 1 master file with event info (dates, times, categories, prices etc). I have another file to track sales which uses index match to pull data from the master file (stored on a server) instead of double entering.
Upon opening the sales file, all the index match cells are blank until opening the master file, even after saving.
Is there a way to make the file retain all the data it pulls without setting manual calculation (manual spreads like a virus to other user's files - finance manager almost killed me last time)
I currently have a macro button set up to open/close the master file, but if I try to filter/sort the data without the master file open it all disappears again.
I'm really looking for a way to get the full address of the external file by specifying the file name in a cell and then evaluate the formula. For example,
[Code] .....
is the full address of the source file called 'Source'. My idea is: if I just type the file name 'Source' in a workbook's cell $A$1, and in cell $B$1 I want to make the formula something like:
="C:Users["&$A$1&".xlsx]Sheet1'!$F$1"
This will make my project very impressive with this feature: a user just types the file name to establish the link and evaluate the formula for results.
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?
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.
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.
