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 put together some code which at the end of it I am wanting to show in cell D1 the creation date for the most recently created file found.
View 5 Replies View RelatedI am wondering how you can change Excel's original file creation date. You can find it by right-clicking on a speadsheet icon then going to:
File - Properties
Custom Tab
Click on Advanced
Look for a field called "Date Created"
Some dates you can edit/change, but I haven't figured out how to change this one yet. I'd like to know because I have a need to change the date on a particular spreadsheet.
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] .....
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 have an excel file with the ID, Name, workID and Creation date fields. Some people have several work ID's created in different times. How can I select row that contain work ID's that were created the latest. I may have several records per person, I need only the latest record. File contains about 3000 records, I can't go through all of them.
View 9 Replies View RelatedI 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'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.
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies View RelatedI am trying to prompt the user for the directory to be saved in and file name to be saved as; then save the workbook in the input directory with the inputted file name.
View 3 Replies View RelatedI have the a file with the below Columnar format:
CoName April May June... till Mar
I have 15 Company Names. I need to create a file for every CoName-Month Combination.
CoName April
CoName May, etc
I do not want to create 15x12=180 files manually.
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.
I was working on a tax file yesturday, just a long long list of receipts. and the file is password protected. However, I went to open it today, and I get a message that says: "Filename.xls cannot be opened. the file may be read only, or you may be trying to access a read only location. Or, the server the file is located on may not be responding."
It's on my USB Drive. I've tried copying it to my HD so I can play with a copy, but no dice. When I try that, I get another error message, : Cannot copy Filename.xls. The file or directory is corrupted and unreadable. I've tried the various fixed suggested in the excel help files to no avail. I did a search on the forum, but didn't find anything that seemed to apply or help me. The computer I'm trying to open it on has Office 2003, the one I most recently worked on the file on has office XP. Not sure if this was a source of the problem. Doesn't seem like it should be. Hasn't been in the past. I'm not even getting to the dialogue box asking for my password yet.
I have a file (I'll call it 'Template' where I am extracting data into a new workbook. I refer back to my Template in my code, but I have now come across an issue. I will save the template using different country names, for example, if I am populating it with UK data, I will call it 'Template - UK'. In my code, how do I pass the name of this file to a dimmed value. So If i have the template - Uk open, can I pass the filename onto a dimmed value? So instead of having
Windows("template - UK").Activate
I want to have something along the lines of (although I tried this way but it didn't work):
Dim template As String
template = Application. ActiveWorkbook
Windows(template).Activate
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?
I have a requirement to download a lot of historical data files from the archieve of an website. The url goes something like this: [URL]....
This downloads file for 21022014 ie. 21-Feb-2014. I need to be able to have a facility to have a selection criteria on my user form (in excel) where I specify a date range and the macro automatically downloads all the valid excel files available within that date range (files for Saturday, Sunday and some holiday dates will not be available in the website archive database) one after the other (like at single clcik of button) into a specific location (predefined viz.. c:/Users/EOD files/) on my laptop. Best would be if I am able to select the save location run time by using a 'Browse' like feature where I go and choose my local laptop folder...same type when we try uploading a file from our laptop to the web..
I have a file that I want to name based on content in certain cells. I.e. "Q-(text from cell G1)-(text from cell B7)-Date(mm_dd_yy)". I want to be able to specify the location where I save this file each time I try to save it, but I want it to update the file's name if any of the parameters have changed.
I have some code generated that does just about everything right except that it automatically saves a version of the file in the folder where the original is located even though it also brings up the dialog box and allows me to save it in another location.
Basically, I want to fix the code below so that it doesn't save what appears to be a backup file each time. Should I be using some sort of savecopyas?
Code:
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Add the quote # to the header
ActiveSheet.PageSetup.RightHeader = "Quote No. " & Range("G1").Value
[Code].....
I'm trying to write code that will open files within a specific folder. However, the end of each filename is going to vary over time to the point that I'll never be able to predict the exact filename.
Is it possible to write something like:
Code:
Set wb = Workbooks.Open "C:\folderfile_filenumberxxxx.xlsx"
Where the "xxxx" signifies the variable element of the file name that could be any four characters? Failing that, is there a similar possible alternative?
I am trying to set a file name as a string to use it as an attachment. The user inputs a number into a textbox. That number is used to locate the file in location. But the file name is different with each file. So im trying to do something like
"C:Desktop*TextBox1*.docx"
Whereas the actual file name is "C:DesktopMCJC RFI 1 MISSING ROOM NUMBERS.docx"
The location "C:Desktop" will always remain the same as will ".docx". I have everything else working just fine.
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 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'm really looking for a way to get the full address of the external file by specifying the file name in a cell and then evaluate the formula. For example,
[Code] .....
is the full address of the source file called 'Source'. My idea is: if I just type the file name 'Source' in a workbook's cell $A$1, and in cell $B$1 I want to make the formula something like:
="C:Users["&$A$1&".xlsx]Sheet1'!$F$1"
This will make my project very impressive with this feature: a user just types the file name to establish the link and evaluate the formula for results.
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] ......
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 RelatedThis link worked perfect for my needs: Create a List of Hyperlinked Files:
But is there any easy way to only show the filename insted of the full path for the name of the hyperlink? Now it shows it like :C:MyDocumentsTestings est1.xls
I only want it to show as: test1. I still want the hyperlink to work
I have a document that I need to track all changes to. I have a macro running perfectly right now that tracks all of the content changes to the form. What I am wondering is if I can add something that would also record when a copy is printed and the user that makes the copy. Here is the code I have right now:
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = False
Sheets("Revisions").Unprotect Password:="Hm72K9"
If ActiveSheet.Name = "Revisions" Then Exit Sub
Application.EnableEvents = False
[Code] .....
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 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?
I have a text file that contains two rows of data for a single record. when I open the file up in excel, it puts each row into column A. What I need to do is put the 2nd row and combine it with the data in the first row so that i can then run the text to columns wizard and put all the data into its own column.
Here is an example of the data:
TEWAC Dresser Rand SAB 21120-14 1350 1687 3300 80 .80 50 4 2.63 EE-7592 1 2250 46 30.5 14 6
10 12 1 4 .229 .102 2 2 .0303175V/1 HL2 .13 .50 .25 1.5 394 3.000
IP23 Andalas SAB 21000-28.5 1500 1875 415 80 .80 50 4 4.22 EE-7777 80/50 Rise 1 2250 43 30.5 28.5 10
2 13 4 8 .258 .102 0 0 3175V 2 .11 .46 .25 1.5 1.500
CACA Intergen CACA 21120-33 1720 2150 6600 70 .80 50 4 4.56 R971055 50C Amb EE-7467 1 46 30.5 33 14
8 14 1 2 .229 .144 2 2 .0303175V/1 HL2 .20 .65 .30 2.0 697 2.150
What it should look like is the following:
TEWAC Dresser Rand SAB 21120-14 1350 1687 3300 80 .80 50 4 2.63 EE-7592 1 2250 46 30.5 14 6 10 12 1 4 .229 .102 2 2 .0303175V/1 HL2 .13 .50 .25 1.5 394 3.000
IP23 Andalas SAB 21000-28.5 1500 1875 415 80 .80 50 4 4.22 EE-7777 80/50 Rise 1 2250 43 30.5 28.5 10 2 13 4 8 .258 .102 0 0 3175V 2 .11 .46 .25 1.5 1.500
CACA Intergen CACA 21120-33 1720 2150 6600 70 .80 50 4 4.56 R971055 50C Amb EE-7467 1 46 30.5 33 14 8 14 1 2 .229 .144 2 2 .0303175V/1 HL2 .20 .65 .30 2.0 697 2.150
leaving me with 3 rows and one column of data.
I have multiple files with about 600 rows in each that I need to process.
I have recorded (i.e. manually as opposed to writing VBA code) a number of macros to perform a routine, however they do not work when I change the filename.
Could someone please advise on how to edit these macros (which reference a specific filename) so that they work when the file name is changed. The macros copy and paste values from different worksheets and then run another set of macros. However all macros and worksheets are located within one excel file