Using Macro To Open Specific File And Execute VLookup Function
Oct 5, 2012
I have to run a report each morning and in once cell I need to pull in the contents of another cell from another worksheet. I'd rather not have to open the additional file each morning to copy and paste special the values so I'd like to add to my current macro to pull this data in automatically. I have to do this for two different reports/portfolios and the data I am pulling in for each portfolio is located in the same exel file but under two different tab names, the portfolio names 2010 and 2045.
Deliverables
The spreadsheet I am pulling data in from is located here:
K:Risk OversightMarket RiskTracking ErrorBARRA
and the file name is: Daily Barra Tracking Error.xls
Tab name would be 2010 for the 2010 portfolio or 2045 for the 2045 portfolio The vlookup will be from "A32:B2500" and I would like to incorpoprate an IFERROR function that returns "" in the event of an error. Column "A" are dates and column "B" is the data I need to pull in.
Receivables I am pulling the data into cell "J23" of a summary report (sheet 2) and would like the vlookup to read something like this:
IFERROR(VLOOKUP("K1"Active.Workbook.Sheets2,[K:Risk OversightMarket RiskTracking ErrorBARRA"&"Daily Barra Tracking Error.xls"(Sheets)]."2010""A32:B2500",2,0),"")
This should return a blank cell if the date cannot be found in the data spreadsheet and return the correct data for teh correct date being referenced in my summary sheet.
how to do the bracketed parts for pulling in the right spreadsheet and tab.
View 9 Replies
ADVERTISEMENT
Oct 11, 2012
I am naming raw data files by the date of the data in the file. In another file I am using sumif and sumifs formulas to consolidate the data and organizing it by date, later to copy and paste special to keep the cleaned orgaized data in one spreadsheet. I would like to be able to reference a date and have excel open the appropriate file (named by the date) and then execute the sumif and sumifs formulas. If this is possible I can have the spreadsheet pull in all sorts of historical data all at once and I won't have to worry about copy paste special.
View 1 Replies
View Related
Nov 16, 2008
I've got a VBA macro set up in one Master excel file that manipulates a number of other files. Both the Master and the other files are all in a folder which is currently called C:Documents and SettingsusernameDesktopPipeline and my VBA script has this defined as the filepath and works exactly as I need it to. I will be moving the folder "Pipeline" to a shared drive on my server so my staff can access it, and will be sending it to colleagues who will be installing it on their own servers so they and their staff can use it. Is there a way to amend the code so that the VBA in the Master file looks in the folder in which it is locate, "Pipeline", no matter what the filepath leading to the "Pipeline" folder is?
View 8 Replies
View Related
Aug 16, 2007
I am using this code to get me to a path then I click on the file I want and it opens it and makes it a .csv file. Then my code does stuff with it and closes it. My question is, do I even have to click on it? Can I get the macro to automatically open the file. Note the "NewestFolderInThePath" is a function that open me up to the "newest" created folder in the path. From there I double click file.
NewestFolderInThePath = ReturnNewestFolder("\datawhse
ootLAW81LAWSONprintRBLEVINS2anrvwfins1")
Path = "\datawhse
ootLAW81LAWSONprintRBLEVINS2anrvwfins1"
TheFile = Path & NewestFolderInThePath
CreateObject("WScript.Shell").CurrentDirectory = TheFile
TheFile = Application.GetOpenFilename("Excel Files (*.*), *.*", , "Open Balance Sheet File FRPMBSDTL")
If TheFile = "False" Then
continue = MsgBox("Do you want to Continue to IS Download?", _................
View 9 Replies
View Related
Oct 11, 2013
I am trying to write a macro to open a specific file, but need to search multiple folders within folders to find it.
The file name I need to open is "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx".
I need to drill down to the Adherence Report folder and then have the macro search through folders for each year (2012, 2013, 2014, 2015, etc) and then each month within each year (1 January, 2 February, 3 March, etc), at which point I would then find that day's file.
So far I have the following:
Code:
Sub Open_ESCL_Report()
Workbooks.Open Filename:=*****.****.****.******.comsharesPurchasingTeam XEscalationAdherence Report & "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx"
End Sub
Each file is stored in it's respective month folder as .....Adherence Report(Year)(Month)(File).xlsx
View 3 Replies
View Related
Jul 20, 2003
Is it possible to use a macro to:
1. Open Excel from the Desktop
2. Open a workbook
3. Execute a macro from this workbook
If so, how can it be done, specially point 1?
View 9 Replies
View Related
Feb 3, 2003
I need a macro that will execute the F2 function key so that the cell can be edited.
View 7 Replies
View Related
Jul 6, 2012
I am trying to open a website, then for excel to download the csv file, then for it to save it in a specific folder under a specific name and file format (excel).
I am successful at opening the website with the following code, but how to do the rest.
Sub Searchez()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://quote.morningstar.ca/Quicktakes/stock/keyratios.aspx?t=clwr®ion=USA&culture=en-CA&ops=clear" 'load web page google.com
IE.Visible = True
While IE.Busy
DoEvents
Wend
'IE.Navigate2 "javascript:SRT_keystuts.exportcsv()"
'this is the name of the download link as from when i hover my pointer over download link.
End Sub
View 1 Replies
View Related
Mar 20, 2014
I want to open a specific sheet and refresh only said sheet when i open another sheet for example x.xls
So opening x.xls will automatically open y.xls
I've tried this in the workbook code area but it doesn't do anything.
[Code] .....
View 1 Replies
View Related
Jun 4, 2009
I'm working on the following
Workbooks.Open Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-" & Ucase(Format(DateAdd("y", 0, Date)), "YYYY-MM-DD")&".XLS"
ChDir "D:CommondataIBMmain"
ActiveWorkbook.SaveAs Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-NAFTA.XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Date: 2009-06-03
What I'm trying too do is open a file, make an exact copy and save it under a new file name.
My problem is in the date formula the day is not always the same. In the sample case it's 03 at other times the day will change.
is there a way too get this too work irregardles of what the day might be?
View 9 Replies
View Related
May 15, 2007
If i have a sheet with multiple tabs can I somehow make excel open that file on a particular tab rather than the tab that the user was on when they saved/closed it.
View 9 Replies
View Related
Mar 13, 2007
I am trying to write a macro to find a file in a specific folder. The file name changes weekly. (Ex. C:My Folder31207.xls). I need to find the most recently modified version of the file and open it.
View 4 Replies
View Related
Oct 18, 2008
I want to create a macro that will “open the look in list” and stop so I can pick a file to open. I’ve tried to use “record a macro” and “ctrl-o”, but the record a macro won’t stop until I pick a file or cancel the file list. I also tried to use “o” in the short cut key box
View 5 Replies
View Related
Aug 29, 2012
I am using below code to open file from 2 diff. folder and trying to do vlookup.
Its working in single loop but not moving to next file
Error: Invalid procedure or call argument.
Sub vFunction()
Dim strTempName As String
Dim strPath As String
Dim wb As Workbook
Dim wb1 As Workbook
Dim strFile As String
Dim strFile1 As String
[Code] ...........
View 2 Replies
View Related
Jul 21, 2014
I was looking over web for a vba code that will open yesterdays last modified (saved) file in a specific folder.
In these folder is for eac day one file, so it has to open yesterday, not today - yesterday last saved (modified).
View 1 Replies
View Related
Nov 21, 2012
I have to open each pdf file in a specific folder, select and copy all contents and paste it in column 'A' of a new workbook.
I have tried following code but not worked...
Code:
Sub ConvertPDF(control As IRibbonControl)
Dim AdobeApp As String
Dim AdobeFile As String
Dim StartAdobe
Dim fso As New FileSystemObject
[Code]...
I have Acrobat Reader installed. Any method without using 'SendKeys'?
View 1 Replies
View Related
Aug 29, 2012
I have a requirement to search workbooks in a particular folder with specific string in file name. For example, let us assume I need to find a file which contains the name 'RR' in it. The position of 'RR' will vary with files i.e. 'RR' might be present either in the beginning, middle or at the end of file name. All I wanted is to search for file with 'RR' and do some activity and close the file and then goto next file. Similarly, the next search has to be performed with the files containing the name 'BB' in it.
View 1 Replies
View Related
Feb 8, 2013
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).
View 1 Replies
View Related
Feb 2, 2007
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 ........................
View 9 Replies
View Related
Aug 21, 2013
getting the username of an unshared file on network. When I click on file I'll get this message "...already open by " its blank.
I have found 1 code but it is not working on office 2013. VBA function to get username of open file on network?
View 4 Replies
View Related
Jun 22, 2013
I will receive one master file which contains the table and it's values and I have another working sheet where I will update the require details. Every time I will copy the entire master data into working file in order to extract the values by using vlookup function. note that the master file has different names each time. So, that if I use vlookup by taking master data path but not getting the results.
I am looking one macro code to select file (master data) and paste the values based on vlooup references in workings sheets. Note this master data file name may change but reference range always same.
View 9 Replies
View Related
Oct 31, 2006
I'm doing a function to comprobate that a cell value has 11 digits. I'm not use only a validation because the the cell value can start with a 0. So I make this function
'This function is to get all the characters from a cell
Function ValidaSeguro(SS As String) As Boolean
Dim strArray() As String
Dim lLoop As Long, lCount As Long
Dim Respuesta As Integer
lCount = Len(SS)
If lCount <> 11 Then
ValidaSeguro = False
Respuesta = MsgBox("Don't have 11 digits")
Exit Function
End If
Redim strArray(lCount - 1)
For lLoop = 0 To lCount - 1.........................
View 4 Replies
View Related
Feb 18, 2009
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.
View 2 Replies
View Related
Nov 6, 2011
I use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.
The file name they select will look similar to this K2271011.504 or K3011111.201
View 3 Replies
View Related
Sep 25, 2009
I have two userform one if useing the getopenfilename to popluate a listbox. and then i use these listbox item withen this code to copy the files into a new folder. This works ok.
Then i copy a program into the same folder jpegresize.exe this program makes resized images of all files in its directory. by defult this program should only deal with these files. though what is happening is instead of using the files in the new folder it running on the files where the originals where copied from. (hope that makes sence). Though if i run jpegresize.exe from the new folder or command prompt it. It runs like it should. So i cant figure it out is there somethink the the shell and wait is passing that it shouldnt be.
View 3 Replies
View Related
Jun 24, 2006
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.
View 9 Replies
View Related
May 2, 2014
I currently have a piece of code that opens all of the files in a folder that are called "*agent*", opens them and copies information. Now, these files come with numbers at the beginning which, are always the same. I only want to open certain files that begin with, for example, 801, 802, 803, 804, 805 and 806. How would I write this into my code? As you can see from the below code, it now looks for the files that all have "agent" in the name, but this is opening files that have that name but are not the right ones. Here is my current macro...
[Code] ....
I hope this isnt as simple as putting "MyFile = Dir(MyFolder & "*801*", "*802*")" etc.
View 2 Replies
View Related
Feb 22, 2010
I'm looking for a way to execute some code, when the user closes the workbook/excel.
So far, I've found this
View 3 Replies
View Related
Jun 18, 2008
how to execute a program by clicking a button I created inside an spreadsheet and also how to load a file into the program I open.
View 6 Replies
View Related
Nov 17, 2006
I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.
Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"
View 3 Replies
View Related
