Select / Open File Based On Today Date In Sheet Name
Sep 15, 2014
I managed to create some code that will connect to a workbook and take the data from there.
It is kind of SQL mixed with VBA. I just got this by finding it in google (I don't have the link handy now).
What the file does, is it will take 2 columns from a workbook called "Test.xls" and then take out 2 columns "Name" and "ID".
The only thing is, this is only a test and not the actual files I need to open. the files are always saved on the same folder, but the name is the day of the export of it. So when I export a file today, it will get 2014.09.10.xls. The sheet (only 1) will also get that name.
How could I change the below code that it will take that file based on today's date and read from the sheet with today's data?
VB:
Sub sbADO()
Dim sSQLQry As String
Dim ReturnArray
Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath As String, sconnect As String
'DBPath = ThisWorkbook.FullName
[Code] ....
View 9 Replies
ADVERTISEMENT
Apr 16, 2014
I have the current code that opens our raw data file and copys it into my workbook. However it now turns out the file is saved everyday as a new file with todays date on the end. Here is my current code:
Sub CopyRaw()
Application.DisplayAlerts = False
Sheets("PROD_SELECTION").Visible = True
Sheets("PROD_SELECTION").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
[Code] .....
Todays file is now in the name "I:SalesSales and MarketingMarketing List Product Selection Details (20140416)"
I know i can do this somehow using the Date function but the closest I have got is using
"YEAR(NOW())&RIGHT(MONTH(NOW())+100,2)&DAY(NOW())"
To generate todays date like so: "20140416" but I dont know how to get that into my code correctly so i open the file with that on the end.
View 1 Replies
View Related
Feb 25, 2009
how can you automatic the date to today's date when you open the excel file?
ie.
Price Report For 02/25/09
View 5 Replies
View Related
May 8, 2014
I have made the macro that does as ,soon as i open the file, select today's date but only if date is in sheet5... sheet 5 is may so it works. Doing the same job for every sheet in the file.
View 10 Replies
View Related
Apr 24, 2014
I need to open an excel file based on a date mentioned in the file name, but minus 1 day. The file name is composed with Statistics_date_time.xls
Example: statistics_20140423_142754.xls
Once I need to open the file (only in the mornings), it has to be the one from the previous day to see the yesterdays performance. How can I create a macro that will look at the date in the file and take the one from yesterday?
This will be a part of a macro I'm making. The rest of the macro will just format the file and combine a few ones from different departments.
View 4 Replies
View Related
Apr 30, 2013
I'm trying to create a workbook which has dates in column B, starting with row 10. I'd like to have a code which will hide the rows if the date is less than today upon workbook open. This would need to apply to worksheet1 only.
View 1 Replies
View Related
Feb 11, 2013
I'm on excel 2010 and I have a small group excel files I open everyday. Most of the files are static in name and location. I've got a macro created to open those files, which works fine with workbooks.open and the file path.
There are two report files I want to incorporate into my macro of workbooks to open. The files are created weekly and the files names have the following format: "Report Name (YYYY-MM-DD).xlsm". I don't want to use the file's last modified date because older files may get edited after the more recent ones are created. The files are also not always created on the same day, so the solution needs to be flexible enough to not refer to a specific day of the week or anything.
Macro open an excel file based on the latest date found in filename.
View 9 Replies
View Related
Aug 21, 2013
Im working on a document for work, its sort of a learning hobby tbh, something i want to learn. Im trying to do things myself from the books etc, vids from the net. This one i cant find the answer to so before i go ahead i would like to see if it can be done
Basically, we have a few documents that change every day. we get the raw data, and delete coloumns, change the size to autofit width etc...
(im creating a main macro to do that now)
But with that data i need to..
open a new book (but the book name is never the same... book1, book2 as you know)
paste the data
save it
.... the file name! needs to be something like this "Department 20130401" which i saved on 01/04/2013
if i did the document on 10th september.. it would be "Department 20131010"
I have Coloumn A with the dates 01/01/2013 etc
and Colounm B with Department 20130101 etc
Cell ("I1") has =Today() in it
could a macro change the filename from a vlookup result?
View 1 Replies
View Related
Feb 16, 2010
I have an infinite list of consecutives dates on a row like the one below. I want that everytime I open my file, the first column shown is the one containing today´s date. If today´s date is 16.Feb, then the first column must be the one containing 16.Feb and so on.
15. Aug.16. Aug.17. Aug.18. Aug.19. Aug.6060606060
View 10 Replies
View Related
Feb 17, 2009
I'd like a macro to have the workbook save as
Numbers as of "today's Date"
and then close that workbook.
I already tried the following...
View 5 Replies
View Related
Oct 17, 2011
I am writing code that requires data from another workbook. Instead of hardcoding the directory and filename (which changes daily), how can I have the code prompt the user to do this for me?
I want to open the file with:
links updated
read-only
Also, is there a way to have an initial directory that appears so the user need not go through a dozen directories every time to get to the file?
View 3 Replies
View Related
Sep 8, 2009
i would like to use the application.getopenfilename to select multiple files, then with these file's would like to be able to use them to create a email with these files as the attachment. then move the files to a new location and delete the originals.
View 2 Replies
View Related
Jan 18, 2009
I'm using 2007 but I need this to work with Older versions so I tried to combine the condition for red
View 14 Replies
View Related
Mar 27, 2014
I need to copy rows to sheet1 to sheet2 which matches with today's date. The code dosen't work wit date. I have attached ScreenShot of excel file.
Excel.JPG
HTML Code:Â
Function Data(strFrmSheet, strToSheet)
Sheets(strToSheet).Select
Range("B2").Select
Sheets(strFrmSheet).Select
Range("B2").Select
Do While (Not (IsEmpty(ActiveCell)))
[Code]....
View 4 Replies
View Related
May 9, 2014
I have attached an example sheet : LATEST.xlsm
To sum up - need a drop down box - that when "flange" is selected - It opens a file automatically - no clicking etc. just when that option in the drop down box is chosen.
[Code] .....
But doesn't work, plus means I have to click on something.. etc
View 7 Replies
View Related
Aug 22, 2007
Was wondering .. as I can not seem to find the right type of coding.
I am trying to have the file open in the next blank cell on column C, ut at least have 5 lines showing above it as there is Freeze Panes.
Is this possible for excel to do upon opening?
View 8 Replies
View Related
Oct 17, 2008
I am trying to project the next 12 month-end dates, based on today's date. I can do that using the EOMONTH function ... see exhibit below ... present month, 1 month out, 2 months out, last month. However, this workbook must be sent to many people and many of those folks will not have EOMONTH functionality because that requires the Analysis Toolpak functions to be added in. How can I accomplish this using standard Excel functions?
Present Month >>> =DATE(YEAR(NOW()),MONTH(NOW()),1)
One Month Out >>> =DATE(YEAR(EOMONTH(NOW(),1)),MONTH(EOMONTH(NOW(),1)),1)
Two Months Out >>> =DATE(YEAR(EOMONTH(NOW(),2)),MONTH(EOMONTH(NOW(),2)),1)
Eleven Months Out >>> =DATE(YEAR(EOMONTH(NOW(),11)),MONTH(EOMONTH(NOW(),11)),1)
View 8 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
Dec 11, 2013
Two months ago our employer seriously limited the size of our mailboxes. We are "forced" to store emails as msg files on our network. PST archives are not allowed. This naturally sucks, as in Explorer you only have the file names and dates to look for that one particular email amongst hundreds (thousands) of msg files. I was able to compose a program in Excel 2010 that generates a list of msg files from a selected folder, together with metadata (sender, receiver, etc.) and so on.
In a test the program worked fine with 6,710 msg files I could post the code here, but the comments and many of the variable names are in Dutch (I never expected it to work in the first place). There is one thing I would like to add to the program. Sometimes I want to add an email to a new email as an attachment. Nowadays I have to look for the right msg file in Explorer by hand, which is a pitty, because I have the paths and filenames in my worksheet. I can put that in a string variable and let VBA do the looking up.
But I don't know how. I would like to be able to let VBA look for the file in Explorer, select it and then stop. I could then drag and drop the selected msg file in my new email. But all my attempts to find example code end up with code that enables a user to select a file and immediately open it. That is one step too far for me. If the msg file is opened, I cannot add it to a new email as an attachment anymore.
View 7 Replies
View Related
Jan 25, 2013
I have found some excellent code that exports rows to individual sheets based on values in a column, and it works perfectly. I have found some code that deletes any rows that do not contain today's date:
VB:
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("O" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
If Range("N" & i).Value < Date Then Rows(i).Delete 'N is column "Treatment Date"
Next i
Application.ScreenUpdating = True
But I'm having trouble adding this to the code I'm already using (that is working):
VB:
Sub Copy_To_Worksheets_2()
'Note: This macro use the function LastRow and SheetExists
Dim My_Range As Range
Dim FieldNum As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim ws2 As Worksheet
[Code]...
I tried adding it after the code " ' delete columns (after exporting from Current Patients)" but I received the error "Compile error - Duplicate declaration in current scope" as it relates to Dim LR As Long.
I have attached my spreadsheet : Daily Treatment Summary.xlsm
View 1 Replies
View Related
Feb 10, 2014
Expanding this code to make it do the following:
1. Start w/ "RULE-Table" sheet,
2. Take note of "TODAY" date,
3. LOOKUP date in "RULE-table"
4. Locate Coordinates found in Col D of RULE-Table,
5. LOOKUP the data that resides in those Coordinates on the "PRODUCTION" sheet & COPY
6. PASTE data into "REPORT" tab C2 and E2.
Here's an example to visualize:
Code will do everything it needs to do within an Undisclosed OPEN file with several sheets. The reason the Workbook file name is undisclosed/undefined is because the file name will vary based on clients, so I don't want the code to be limited to look for a specific filename.
*The sheet names within this OPEN client file will always have the same names.
The sheet called: "RULE-Table" holds coordinates based upon "TODAY's" computer date.
For example: If today is JAN 4, the desired coordinates to pull data from are "B5" and "D10" from the "PRODUCTION" sheet.
"RULE-Table" sheet
A......B.........C................D.......
Row1.DAY....WK RANGE....QTR RANGE
2......1/1.......1...............B2,D7
3......1/2.......1...............B3,D8
4......1/3.......1...............B4,D9
5......1/4.......1...............B5,D10
6......1/5.......1...............B6,D11
Since today is 1/4, its rules say go to B2 and D10 of the PRODUCTION tab and collect the data
"PRODUCTION" (data source) sheet (within the same open file)
A.......B......C....D....E....F...
Row1..Header..Hdr..Hdr..Hdr..Hdr.
2.......2.................................
3.......14...............................
4.......13...............................
5.......20...............................
6.......62..............................
7....................76.................
8...................184................
9...................375................
10.................410................
11.................525...............
Last, the collected data from those 2 coordinates should always get pasted onto the "REPORT" tab into C2 and E2 in this example.
"REPORT" (destination) sheet (within the same open file)
A........B.........C.....D....E.....
Row1..Header..Hdr..Hdr..Hdr..
2...................20........410......
View 3 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
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
Nov 26, 2008
i'm having a problem with a script I have (script is below). What i'm doing is running a script that displays files in a certain folder, those files are then displayed on sheet1 column O and are linked so when they are clicked upon, the file will open. I also have a combo box with a input range of $O$O. That puts the values in column O in the combo box. Now here is a problem and request.
Problem first, when I select the file names from the combo box, they don't open the file, all it does is display the file name in combo box and that's it. how to I get it to open up the file? Now for a request, I have another sheet that is almost exactly as the one i described above. The only thing different I want is when someone clicks on a file name from the combo box, instead of it automatically linking to the file, I want them to click on a button to display the file.
Sub HyperlinkXLSFiles()
ActiveSheet.Unprotect
Dim lCount As Long
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "c:Sec"
.FileType = msoFileTypeExcelWorkbooks
' .Filename = " Book*.xls"..................
View 2 Replies
View Related
Jan 11, 2013
I am looking for the code that I would use so that when the workbook is opened it would always open with "Main" sheet.
View 4 Replies
View Related
Nov 20, 2009
I have designed a spreadsheet and i want a seperate worksheet (sheet3 for arguments sake) to retrieve customer data from worksheet 2 - The data I required is the customer data currently contained on columns A - H and there are around 50 rows. (A2 - I51). I want the seperate sheet to identify entries that have today's date in column I and then list them in Worksheet 3.
Im having difficulties with the syntax for retrieving the data from a seperate worksheet. There may be several entries for the same date and I want to the seperate sheet to report all customer data in worksheet 3? Also, if the date falls on a weekend I would like to retrieve any data for the weekend on the Monday so all cases can be reviewed.
View 4 Replies
View Related
Jul 26, 2012
I am trying to write a macro to complete a report for me. I have everything working and tested but I have 1 issue. The report needs to take some data from yesterdays report.
Therefore I need a code which is able to open the file, I know how to do this with a constant file name but the issue arises because the file from yesterday is saved with the date.
i.e. filename250712 etc..
View 3 Replies
View Related
Nov 19, 2004
Probably not possible but can you write a macro to load a file which has a file name which always starts with Act2July followed by the date
i.e. Act2July191104.xls
but next friday it will be called Act2July261104.xls
This file is created by the network.
So can the macro pick up the Act2July part of the file name and then select the file dependent on when it was saved, so that the latest update is always loaded.
View 9 Replies
View Related
May 9, 2006
I have moved this query to a seperate post because the other was becoming a bit bogged down.
This code supplied by RichieUK works perfectly until the month changes then it goes wrong. I have run the macro for every day of May up to the 3rd June using dummy files and then it goes wrong -
When I get to the 3rd June dsecondlatest which should pick up either yesterdays or (last fridays file if yesterday was a Sunday) picks up the file for the 1st June instead of the 2nd June.
dlatestdate still works properly and gives the filename for the 3rd June.
Sub New_Coupon_Count_Update4()
' Find Source Data File
ChDir "J:\_RU_REPORTSMARKETING"
Const strMyDir As String = "J:\_RU_REPORTSMARKETINGCOUPON COUNT CSV"
Const strFirstName As String = "Ru0230_"
Const strSecondName As String = "J:DAILY_COUPON_COUNT_2006COUPON06.xls"
Dim strFName As String, wbk As Workbook
Dim strDate As String, strDay As String, strMth As String, strYear As String
Dim dDate As Date, dLatestDate As Date, dSecondLatest As Date
Dim bFirst As Boolean
Dim vaFileName As Variant
Dim daysdif As String
Dim dLesResp
Dim dTodResp
Dim Ro As Long
Dim Col As Integer
Dim DataRows
Dim DataRows1
Dim DataRows2
Dim DataCols1
The rest of the macro after this point works perfectly.
View 9 Replies
View Related
Aug 6, 2013
I have a csv file named "BB31_2013-08-01". I would like to open this file via vba and this is not my only file. I also have another similar fileames such as "BB31_2013-08-08", therefore, I try to create some function like yyyy, mm & dd where I can modifie it easily for next file.
Here is my code:
VB:
'change weekly
yyyy = "2013"
mm = "08"
dd = "01"
Workbooks.Open Filename:= _
"C:Documents and Settings1314228DesktopTest ProgressBB31& yyyy &-& mm &-& dd.csv"
[Code] .....
View 3 Replies
View Related
