Open File With Date In Filename
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
ADVERTISEMENT
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 1, 2008
I would like to create a macro to open a file in Excel 2007. My problem is that the filename changes often. After updates are made the filename is changed and the old file is moved to a backup folder. So, there is only one similar file in the folder
My filename is "RFQ Worksheet 7 29 2008.xlsx".
The path is "C:Documents and SettingsahaynijDesktop".
View 5 Replies
View Related
Mar 11, 2014
I have a Workbook "forecast.xlsx" and several Workbooks which have a individual Number in the Filename (E.g. "3960........xls", "3961.......xls etc.) in the same directory as "forecast.xlsx" + projects"
Now, I would like to browse through "forecast.xlsx" in column H, searching for hyperlinks. In Column H are these Projectnumbers located which are used in the filenames above. Whenever the Cell cointains a hyperlink, following should happen (that far i managed it by myself):
- Store the Cellvalue in a String Variable
- Go to the Directory this.workbook + projects
- browse this folder, searching for a File which contains the stored string in its filename
- open the file
- copy a range
- close file
- This.workbook.Activate
- follow the Hyperlink which has been stored as String before
- paste selection
- continue browsing through column H, looking for the next hyperlink.
I have a VBA which runs bugless, but it copies the wrong range, but to the correct destination.
See the code below:
Sub RESLT_INPUT_ALL()
Dim cell As Object
For Counter = 1 To 1000
Set cell = Worksheets("Projects overview").Cells(Counter, 8)
If cell.Hyperlinks.Count > 0 Then
[Code] ......
View 1 Replies
View Related
Aug 1, 2006
Am working on a procedure to open all files in a folder and copy some data from the files into another workbook (master). I have this piece of the code. However, I also need to get the filename (from which data was copied) and paste this into an adjacent cell in the master (so I know which file each data point came from).
View 8 Replies
View Related
May 26, 2007
I am trying to open a workbook with
Workbook.Open filename:="xxxxx"
The address in the xxxx is something like this "G:AnimalMammalZZZZGorillayyyy.xls"
The ZZZZZ portion is the result of an InputBox.
What is the syntax for placing the result of that InputBox in the middle of the address?
View 3 Replies
View Related
Mar 21, 2014
I have a template file that I import data in to, which is then saved with the current date. This works, but I want to automate the import of the data, but ensure I don't duplicate the load.
Therefore, I have a tab 'FileLog' that I record the filename (variable by date) in column A and Creation timestamp in column B.
How do I obtain the filename and creation timestamp of the import data so that they can be entered?
View 11 Replies
View Related
Aug 9, 2012
I want to create a macro that when executed changes the name of "File1" to "File1 - Edited 8-9-2012". With the date being today's date (variable). This file is going to be emailed to someone who will then execute this macro.
Is there a way to prompt the Save As window with the updated file name so the user can save the file where ever he/she wants?
View 9 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
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
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
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
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
Apr 2, 2014
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?
View 1 Replies
View Related
Aug 30, 2013
I was wondering if there is a way to write a macro that will take an excel file that I have called Alldata and then have it copy the first two sheets to a new workbook named something else( FullSparameter_0) and then copy the name of the third and fourth sheet into the new FullSparameter_0 workbook and then copy column A-E from sheet dB Alldata.xls and paste it into the FullSparameter_0.xls workbook in the same dB sheet in the same spot. And do the same thing with the Phase Sheet. i know this sounds confusing, but im really new to the VBA stuff.
I just need 15 different files from the original Alldata.xls that will be named FullSparameter_0, FullSparameter_1, .., FullSparameter_14. but each file will have the same two first sheets, Setup Information and Cal Verification, and the same last two sheet names, dB and Phase, but then in the dB and phase sheets, it will copy over the next four columns of data. so in FullSparameter_0 it will have column A-E from sheet dB in Alldata.xls in the dB sheet in FullSparameter_0 (Same with the Phase Sheets). Then in FullSparameter_1 it will have column A and Columns F,G,H and I (for dB and Phase). In fullSparameter_2 it will have column A and then J,K,L and M. Each FullSparameter_X will have the exact same first two sheets and then the other columns for the last two sheets will be pasted into columns A-E in dB and Phase.
View 1 Replies
View Related
Aug 6, 2014
I have this code, it's working to open the last modified file but how can I implement that it also opens a certain file name.
examples
Test1 150126.xls
Test1 150128.xls
Test1 150140.xls
the numbers are the time the file is saved, is it possible to also look at file name:text1 and then look at the last modified file So only fill in the file name and the macro opens file: Test1 150140
[Code] .....
View 5 Replies
View Related
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
May 29, 2014
I need a code which will open up a previous days file. I have the code to pick the correct file but I am stuck because the folders are multiple levels down to year then month.
Eg. Folder Name > YYYY > Month
So i need to have a code which can look in to the relevant folder based on the current date and then be able to open the file i need.
I.e. today is 29May so i need the code to open the 2014>May folder.
For 1st day of month I would need it to open last months folder I.e. 01June Open May folder
View 2 Replies
View Related
Jun 5, 2009
Is there a way to open a file similair to
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)
View 5 Replies
View Related
Feb 29, 2008
I have manged to search for the last updated file on a folder but i want to use the filename as the source for a copy and paste. it all seems to work bar the linking of the 2 processes the error message 'run time error 9 subscript out of range' is the file is already open should it just not use the same variable to copy the files?
Option Explicit
Sub Get_Availability_Data()
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Dim wsTo As Worksheet, wsFrom As Worksheet
Dim myDir As String, fn As String, a(), n As Long, myFile As String
Dim myDate As Date, temp As Date
myDir = "C:BarkingEMCIN"
fn = Dir(myDir & "*.csv")
Do While fn <> ""
temp = CreateObject("Scripting.FileSystemObject").GetFile(myDir & "" & ................................................
View 9 Replies
View Related
May 5, 2008
I'm looking for a code which is checking the file name first before opening and operating on it. File name format is filenameyyyymmdd.xls but the problem is files in that directory are saved irregularly (couple times a week) and I need to open the newest saved file.
View 2 Replies
View Related
Jan 23, 2008
I am trying to skip over any errors associated with the GetOpenFilename window, specifically where the Cancel button is selected, or no files selected. In the code below, I get an error "Object required" at the line shown every time this is run, even when the file path is valid and correct, and then the msgbox appears as a result of the On Error statement .
myExcelFilePath is dim Variant
On Error Resume Next
myExcelFilePath = Application.GetOpenFilename("Excel files (*.xls), *.xls", , "locate Report.xls....")
If myExcelFilePath Is Empty Or myExcelFilePath Is Null Then
View 9 Replies
View Related
Apr 14, 2009
Is there any solution to the fact that hyperlinks in excel with a # in filename wont open?
View 9 Replies
View Related
Jun 2, 2009
I try to make a macro for saving an Excel sheet to a .CSV file with "@" as delimiter.
At the line "Open fname For Output As #fnum", the Error 53, "no file found" occurs.
The Excel-help is saying that if the file is not present, a new file will be made.
This error occurs now and then, How to proceed?
Full macro:
Sub SaveAsCSV()
Path = "O:actueel"
Fname1 = Path & Range("E2").Value & ".csv"
fname = Application.GetSaveAsFilename(Fname1, "CSV bestand (*.csv), *.csv", , "")
If fname = False Then
MsgBox "Macro Geannuleerd"
Exit Sub
End If
View 9 Replies
View Related
May 13, 2007
Below is the code that references a file to import = Jan.txt.
There are different files I want to import, using this macro.
However I have not been successful with creating a variable
and then creating a statement which requires me to input the filename that I want to import for the macro run.
With ActiveSheet.QueryTables.Add(Connection:="TEXT;U:Jan.txt", Destination _
:=Range("A1"))
View 6 Replies
View Related
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
View Related
Sep 23, 2013
My company has several users that access a file (File A) that has a macro in that opens another file on their F drive called TEST.XLS. The code (in File A) to do this is:
Workbooks.Open Filename:="F: est.xls"
Just recently this code has failed for some users but not others. For those where it failed I confirmed that they do indeed have the file located on drive F.
As a test, one of the users where the above code failed ran the macro recorder while opening the file (Test.xls) on their F drive. The recorder produced the following code:
Workbooks.Open Filename:="\companynetsta$jsmith est.xls"
It appears the code is failing because Excel is looking for a UNC path as opposed to a drive letter (F).
I prefer that the code that looks at the Drive letter F be used.
How can I get the following code to workWorkbooks.Open Filename:="F: est.xls" ....for all users that have an F drive?
View 6 Replies
View Related
May 31, 2013
I have a tool file which contains Excel VBA codes. When I click button it asks to select a file. I want that anytime I click the button the dialogue box should open in the folder containg the tool.,
View 1 Replies
View Related
Oct 7, 2008
I'm attempting to copy some files into an archive folder in the same main folder. These files appear weekly in a folder that contains numerous other files.
Each of these files has a filename such as:
RAH_MRL_TOP_40_(ddmmyy_TIME)
with time being the time that the report was run.
Every week the filename stays the same but the time and date changes. I was hoping to be able to simply use the "Filecopy" command and specify the source file with a "*" after the main file name. However this does not seem to work.
Is there a simple way to do this? (I dont want more than a few lines of coding really as I will be duplicating it for each file and then changing the file name as needed.
View 9 Replies
View Related
