Anyone know of a way to capture who (user name, machine name etc.) had the excel file open when a certain cell is selected? a Macro?
Trying to use an Excel spreadsheet for a peer review where each reviewer accesses the Excel peer reivew file and enters the minutes they spent in the peer review but would like to also capture the user that had the file open when the minutes spent on the peer review were entered. This would provide proof that the actual peer reviewer opened the file and entered their minutes.
I want to place the atribute (Date as value): "LastAccessDate" of a file called "!FileSessionStart.nwd" in Sheet1.Cell A2 in my workbook - without opening/accessing" this "C:/so-and-so/!FileSessionStart.nwd" - just want to read and use the last accessed date of it.
How will a macro go finding this atribute of this particular file and place this atribute as value as described?
Workbook A has VBA code. The VBA code opens a collection of 2000 workbooks. The workbooks are processed one at a time, with Workbook A opening Workbook B, do something, close Workbook B, get next workbook. Sometimes Workbook B has some kind of access issue that results in:
"[File Name] cannot be accessed. The file may be read-only, or you may be trying to access a read-only location. Or, the server the document is stored on may not be responding. RETRY ... CANCEL"
If RETRY is clicked, the process returns the same message. If CANCEL is clicked, the process is crashed, Excel is closed, and all data processed is lost. What is the VBA syntax to programmatically trap this condition so it can be processed in an orderly manner?
I am using the following code to browse to a CSV file and import it into my current workbook. How can I “capture” the name of the text file (myFile) for future use?
myFile = Application.GetOpenFilename("All Files,*.csv") If myFile = False Then Exit Sub End If
' Open file With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myFile, Destination:=Range("A1")) .Name = "DATA" .FieldNames = True...............
i have used two form and a macro in a workbook. First one captures data via text box. This data is captured in a cell. Form two adds a new sheet with the name that is captured in a cell, then perforns some task in the newly created sheet. Macro does the formatting of the newly created sheet and saves the sheet. Again when i reopen the the workbook the value is captured in next blank cell and the procedure follows. This works fine as far as there is a single. But not in case of multiple user at the same time. Is there a way to directly capture the value to form 2 and macro so that multiple user can work on the same time.
I'm trying to do is definetly simple to a lot of you, but I'm struggling on how to get started. I attached a workbook that clearly shows my work! I'm almost there but not quiet. Basically the user will be able to make a slection that will be used to lookup values from a different sheet. Each selection triggers a ranking number at the bottom (red pattern) in the worksheet. Now I would like to capture all scenarios and display the ranking.
I'm using Excel 2007, and when I add attachements to a spreadsheet cell (e.g. a saved email) nobody else can open the file when viewing the spreadsheet. Is there a way around this?
Currently I'm inserting it by going Object > Create from File > Browse > Select File > Display as Icon. But if the spreadsheet is emailed to anyone else or put on a staff website nobody can open the attachements.
Is there any way to have a input box pop up after a Prescribed number of days or on a particular date and from that point on prompting the user to enter a password that I created (hard coded into the code)In order for the workbook to open If the password is not entered or is not correct then the workbook will close.After the pasword is enterd then the workbook would open from that point on(the input box would be disabled) Also this macro would have to be password protected in order to prevent the user from accessing the code.
I was asked to create a userform for the use of our whole team which would enable them to select a name from a dropdown list and then it would display their business contact details in the rest of the form.
I'm running Office 2010 and Windows XP server/enterprise edition (not quite sure which). After some research I've managed to use VBA code to open the userform while hiding the workbook, then when the close button is clicked it closes the user form and it's associated workbook using the code below.
Code: ' ThisWorkbook module Private Sub Workbook_Open() Application.Visible = False UserForm1.Show End Sub 'UserForm module Private Sub CommandButton1_Click() Unload UserForm1 ThisWorkbook.Close (False) End Sub
This works great when the excel file is accessed directly. However when the excel file is accessed via the link on my teams Sharepoint site it doesn't. When the Userform is closed the underlying workbook and data is then displayed which then has to be closed separately.
I have an excel workbook that uses a .bmp file [a picture]. The master excel file and the .bmp file are in the same directory on the network. The excel file is "Read-Only", so the user would open it, customize it and save it on their own computer or somewhere else on the network.
They are only allowed to save it under "Save As". Once the user saves it, they would then be able to use their customized copy for future updates etc. The problem is that the .bmp file is missing from this new directory.
Is there a way to automatically save a copy of the .bmp file under the Directory specified by the user, so it's always available when the Excel workbook is opened? Or is there a way to attach the .bmp file to the excel file, so they are always saved together?
ive created an excel spreadsheet with expiration dates in one row and im trying to write a code which will pull out all information in the column if the expiration date in a certain row is within 90 days of the date the excel file is accessed
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons. Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later): Template file name: template_order.xlsm Template file location: \servershared emplate
Total list file name: total_list.xlsx Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10 The column heading for order data is located at A11:N11 The selection to be copied is A12:N550 - But only rows where column A includes data (not empty). (If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
I have a requirement to download a lot of historical data files from the archieve of an website. The url goes something like this: [URL]....
This downloads file for 21022014 ie. 21-Feb-2014. I need to be able to have a facility to have a selection criteria on my user form (in excel) where I specify a date range and the macro automatically downloads all the valid excel files available within that date range (files for Saturday, Sunday and some holiday dates will not be available in the website archive database) one after the other (like at single clcik of button) into a specific location (predefined viz.. c:/Users/EOD files/) on my laptop. Best would be if I am able to select the save location run time by using a 'Browse' like feature where I go and choose my local laptop folder...same type when we try uploading a file from our laptop to the web..
I am setting up a "save as" macro that saves a file by replacing another file in a folder. Even though the macro has been recorded by approving the replacement (the prompt appears "the file --- already exists. Do you want to replace the existing file?"), when I run the macro, I am again prompted about replacing the file. Is it possible to avoid the prompt so the file is automatically saved by replacing the named file?
I do routine tasks every day which involves opening 3 different files and pasting data into my main file. The data is always in the same format, and the 3 files are saved in the same location (3 different folders through). The 3 files are saved each day and the naming convention is constant, with only the date changing. For example, the files are always saved in C:My Documents and the files are called test_05.02.2013.xls. Tomorrow the file will be called test_06.02.2013.xls and so on.
So each day I will be rolling a file forward and I want to bring in the info from each of these files based on the new day.
How to do 1, and I will do the others (because the concept will be the same).
I have a macro which successully saves a worksheet as new file to another file path....(below)...but I can't figure out how to close this new file and return to the original file...
Dim myPath As String, fName As String myPath = Sheets("Date").Range("C8").Text fName = Sheets("Date").Range("C9").Text Sheets("Sage CSV File").Copy With ActiveWorkbook .SaveAs Filename:=myPath & fName End With'
I have a base excel file for summarizing some data, the problem is that the data comes from a different excel spreadsheet. What I want to do is make a function that pulls the data from another spreadsheet into my base file. It would be easy if it were just one excel sheet, but this job would require where the data is pulled from a data file which has many modified versions.
Can anyone tell me how to do this? The files with the data will be structured the exact same but with different data entered in. I just want a button so I can click the file I want the data from and have it show up on my summarizing base file.
I need to set up an automated process to open an existing .xls file, extract the data from specific cells located in that file and insert it into a totally different file that I have preformatted to accept this data.
Example scenario:
If I have "that_file.xls" and it is unopened, but it contains data in cells "A2 thru F2" that I need to extract... then I have "this_file.xls" , which is open, and set up with all my calculation cells and a specific layout (lets say it has cells "D8, E9, F10, G11, H12, and I13"). I want to be able to add a button to "this_file.xls" to run a macro that would open a file search window where I can select "that_file.xls", and then it will automatically extract the data from "that_file.xls" into "this_file.xls" as follows:
A2 to D8 B2 to E9 C2 to F10 D2 to G11 E2 to H12 F2 to I13
I want to create a macro that will select all the worksheets (names and quantity will vary) and saves the file as the current file's name but in PDF. Since I only know how to record a macro it specifies the worksheet names but I need it for various workbooks. The name will vary plus the number of tabs can go anywhere from 3 to 40.
Set CopyRng = Wkb.worksheet1.Range(Cells(RowofCopyworksheet, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
I am trying to copy the first sheet in each file in the designated folder and paste it into a master worksheet.
Below is the code.
'Description: Combines all files in a specific folder to Format File for Upload.xls Sub MergeMultipleFiles() Dim path As String, ThisWB As String, lngFilecounter As Long Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet Dim filename As String, Wkb As Workbook Dim CopyRng As Range, Dest As Range Dim RowofCopySheet As Integer
I presently have a macro that, when run, takes to conents of C4 and C6 and saves a new version of the file being worked on into a folder on my desktop. I love the macro with the exception of one part: I don't want to be prompted to overwrite the file if it already exists. How can I change this macro so that, when pressed, it overwrites the file without prompting the user and waiting for their answer?
When it opens the desired file, it increments the file name by 1 each time it is opened (via the macro).
Example; The first time it is opened you see the file name in the header read "P'Binder L&T Pages" for a moment, then it changes to "P'Binder L&T Pages1"... I close the file and open it again (via the macro), and see the file name in the header read "P'Binder L&T Pages" for a moment, then it changes to "P'Binder L&T Pages2"... The file name continues to increment on every opening until the PC is rebooted, then it starts at 1 again.
Sub OpnLTpages()
Dim wb As Workbook Dim AlreadyOpen As Boolean
AlreadyOpen = False
For Each wb In Workbooks 'Scan open workbooks If wb.Name = "P'Binder L&T Pages.xls" Then ........................
I already have code that I believe is supposed to do what I'm trying to accomplish. However, it doesn't fully work. The Macro is below:
VB: Workbooks.Open Filename:="C:Documents and Settingsplp138DesktopExample 1.xls" On Error Resume Next With Workbooks("Example 2.xls")[code]....
Basically, what I'm trying to accomplish is to have a certain range from one file copied, and pasted in the next available (empty) row of another file. While debugging I see that everything is working except for the actual pasting.
I am trying to save my workbook in a specific directory with a specific filename and to incorporate a date field from within the spreadsheet. I have changed the format of the cell so it does not include / as i know this would not work. Saving it is the easy part but adding in the field from the spreadsheet is where im falling over.
for example we have AS1 cell in which appear first number 15, then it change to 5, then it change to 15, then it change to 11 etc. What i need is to capture and lock just the first shown number (in this ex. it has to be 15) and put it to another cell (for ex. cell AT1) Maybe there is any formula ?
I have a lot of workbooks with a date as a name (eg. 2004-08.xls or 1999-03.xls). I would like to capture the date portion of this name with a formula in sheet 'Date' cell 'A4'.
