fMask="Post Biz Voice Installs QC Report*.TXT"
varFiles = Application. GetOpenFilename("TEXT Files,"&fMask, 1, "Please select all the Post Biz Voice Installs QA Report files", , True)
I want to filter the filenames to only certain prefixes
I am then importing the files and populatin the ws
Here is my whole module so it can be seen in context
Sub GetBCDV()
Dim varFiles As Variant
Dim SourceBook As Workbook, TargetBook As Workbook, strAddress As String ', 1 As Integer
Call Archive_WB_BCDV
fMask="Post Biz Voice Installs QC Report*.TXT"
varFiles = Application.GetOpenFilename("TEXT Files,"&fMask, 1, "Please select all the Post Biz Voice Installs QA Report files", , True)
1 column in my sheet is a list of dates (affectively a calendar). I am trying to create a macro that will only display rows 1-5 and then current the current date and the next 30 days and hide all other rows.
The approach I have tried is to use “ADDRESS(MATCH(BS40,$B$1:$B$462,0),2)” equation to find the cell with todays date in it (Cell BS40 contains todays date). This was repeated for todays date +30. The problem I have is that although I now have the cell address’s located I cannot reference their values in a range.select
I know that the below code goes some way to opening a text file that I'm importing. The thing is, I want it to do this every day and work out the filename itself.
This should be simple as the filename is based on the date.
With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;c:docs oday_06062007.txt", Destination:=Range("A1"))
All I want to do is have the start of the filename: 'C:docs oday_' and add the date in the same format automatically...
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 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.
I have column B with a heading "Days Remaining" and column L with a heading "Deadline". starting with row 5, I need to be able to enter a date in L5 and see the days I have left, from that day untill today, on B5. I need to then be able to enter a date into L6 and see a result in B6 and on and on. Then I need to be able to insert or delete a column and have the formulas still work in the columns with the heading "Days Remaining" and "Deadline"
I have a a planner made using excel. Monday to Sunday are from G3 - M3 and the months are in F3 going downwards. The months are abbreviated to 3 letters e.g. Jan, Feb, etc.
Where months cross over in the same week this is indicated as month/Next month e.g. Jan/Feb. Within these fields I have manually entered the dates, so for example todays date of 19th Feb is located on cell J11, (the coordinates of Thu (in J3) and the month of Feb (F11) (showing on week 8). To indicate todays data on the planner I have used an autoshape and simply filled the colour red, so that it stands out. However, when I open the planner the next day I have to move the autoshape to indicate the correct date.
The problem is, if I want to open the planner today, next week or next month (or whatever date in the current year) could a macro be devised that will indicate the correct day (i.e. by increasing the text size, change the font, color, etc?) using the exact headings as I have mentioned? I am assuming a macro on open would work which would look at todays date, and convert this to both a day in the week and month. Once done it could then find the exact day?
I have todays date in cell A2. I have a range of dates in a row 4 and numbers below each date in row 6. What i want is to Sum the numbers below the dates in row 6 if the date in row 4 is with in 90 days before or after the date in cell A2.
Example......
The answer i should get is 4 since only 3 dates fall 90 days before or after todays date.
Im designed a form in excel, which has a calendar date picker to select a desired delivery date for the user, which makes it easier to use for the user, than using a text box.
Ive searched a number of websites and so far havent seen what I need.
I want the calendar, once my form has been opened, to automatically change to the current date.
So in summary, the code, I believe would look something like this:
I am trying to write a simple code that will take the last date in Col A and fill down until it gets to todays date.
Code: Sub Fill_down_date() Dim x As Integer lr2 = Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row For x = lr2 To 20 If Cells(x, 1).Value < Date Then Cells(x + 1, 1).Value = Cells(x, 1).Value + 1 If Cells(x, 1).Value
way to have the vehicle registration date compared to todays date and have it return either due next month, due or over due.
Todays date is in format month/day/year, registration is only by month/year. Todays date is in fixed location in worksheet updated by function Today()
Basically a formula that resembles =If(registration date < todays date, overdue, If( registration date = todays date, due, If(registration date = todays date+1month, next month, If( registration date > todays date + 1 month, 0))))
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.
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.
I have the below code that when the spreadsheet opens input boxes pop up. The first asking to put the date in, but how can i make sure that the user does not put "todays date" in at anytime! (the date put into the inputbox (cell d2) should always be less than the date in b2 = todays date). As cell b2 in the sheet "97008390" already has todays date in. (using =today()).
i would like to be able to save my new workbook as "SL" with todays date next to it. so if i run my macro today it would save as SL 11/09 if i run it tommorow it would be SL 12/09
My datepicker automatically defaults to the date when it was first set up. I would like it to always show the current date when the user form is first opened...
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.
Previous to a PC meltdown a few months ago, when I opened my monthly customer list file the account date would automatically update to the current date. After the PC repair, reinstall of MS Office 2002 (same version as before the PC death) and copying of documents back to their old locations, I now have to update this manually for all accounts each time I open it. I have searched through the options and customize menus but there is no mention of this setting. Before, it just did it from day one (5 years ago) without any setup.
How do I get back the old auto-date update preference?
I'm trying to set up a template running loads of formulas. One of the aspects I want is that a specific field refreshes with the current date, but always reverts to 10:00:00 (10AM) as the time.
So i have this macro that i been using for long time everthing works as expected but i will like to change the saving part to save as specific name plus todays date
Sub EQUIPMENT_RETURNS(control As IRibbonControl) Columns("A:A").Select Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Delete Shift:=xlToLeft Columns("D:J").Select Selection.Delete Shift:=xlToLeft Columns("C:C").Select Selection.Cut Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("B:B").Select...........................
I need a way in VB to say tell me the amount of dates that are less than todays (09-Dec-09) date. Using the above list the answer should be 2. Every formula I try returns the value 0.
What do I have wrong with this? I want to find a cell that matches today's date and then start with that row by copying a time value in another worksheet to the one that has the dates already.
Sub PunchClock() 'This will copy the current time punched and place it into the correct cell Range("B3").Select
Do Until ActiveCell = Now() ActiveCell.Offset(1, 0).Select Loop
All I need is some direction with this. I tried setting up a variable then check that variable against the current date and I didn't have that one figured out either.
i am building a data base and every day, say, at 5pm a certain cell should contain a corresponding date. In my macro I simply wrote
Range("A1").Offset(i-1,0).Formula="=Today()"
I thought naiivly it would work, but it doesn't, as the next day the date will be changed correspondingly. So, the question is: how to record todays' date (Dec 29) so that this date stays in this cell forever? Only the date, not Excel Dates & Times
I would like to include a filter in my macro that shows orders with current date, and sometimes current date minus one (yesterday). This is a monthly recurring task. In the custom filter I tried: "equal to @Today" but that didn't work. Can someone tell me how I can solve the problem? Here's a snippet from my macro, filterne on something else Selection.AutoFilter Field:=21, Criteria1:=">=89" 'STCD_TO_TODAY
