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.
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.
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".
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
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).
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?
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?
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
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.
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 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.
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:
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)
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?
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 & "" & ................................................
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.
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
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?
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
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
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:
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.