External File Properties In Cells. Last Saved Time
Feb 2, 2010I was looking for a way to get external file froperties into a cell!
for example:
Last modified date.
this is what I allready found/tried.
I was looking for a way to get external file froperties into a cell!
for example:
Last modified date.
this is what I allready found/tried.
vba to save data in cells into file name? The vba below works for one cell, but I need info from cell “E11” also with the text MARK between them. myFileName = "2009 CROP BALE CUT OUT FORM – INVOICE " & Format(ActiveSheet.Range("F12").Text) & ".xls"
The final file name should look like this: 2009 CROP BALE CUT OUT FORM - INVOICE 8D4512 MARK CB34.xls
I have a file that gives some statistical data to my co-workers every 15 minutes. A common question I get is "How does that compare to last week?" Then I have to open the file from 7 days ago, find the data from the same time interval, and subtract it from this week's number in my head. I'd like to have excel do this for me.
I know how to get data from an external file. The problem is, these files are named with a date on the end of the file name. So tomorrow, the static formula won't work anymore (or rather, it will give data for a file from 8 days ago, instead of 7). I'd like to excel to use today's date, find the file from 7 days ago, and get the data from that file to compare to the current file.
Here's what I've done so far:
Code:
ThisDate = Range("C1").Value 'the cell with todays date in it
ThisDateName = Format$(ThisDate, "yyyy-mm-dd") 'now formatted the way I need it
ThisDate7 = Range("G1").Value 'the cell with the date 7 days ago
ThisDateName7 = Format$(ThisDate, "yyyy-mm-dd") 'formatted correctly
ThisDate14 = Range("G2").Value 'the cell with the date 14 days ago
ThisDateName14 = Format$(ThisDate, "yyyy-mm-dd") 'you know, in case of a holiday 7 days ago
I have no clue what to do next. I want Excel to: Use "ThisDateName7" to find the file with the name "pph_tracker_[ThisDateName7].xlsm"Get data from a cell in that file (say, C15)Subtract it from the data in the same cell (C15) in today's file (this week - last week)Give me the result in today's file (say in cell C20)Do that again for cells D15, E15, and so on (result in D20, E20, etc)
I assume I can figure out the rest from there. Can I use the variable names in an actual formula in cell C20? Something like:
=C15 - '[pph_tracker_{ThisDateName7}.xlsm]Sheet1'!C15
or even
=C15 - '[pph_tracker_{The Date in Cell $G$1}.xlsm]Sheet1'!C15
which would require no macros at all!
I keep getting a "type mismatch error" for this:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Len(Range("E18:E34")) = 0 Then
MsgBox "You must complete cell E18 thru E34 before you can save this file!"
Cencel = True
End If
I am trying to set a range of cells to ensure they are completed before the file can be saved. I can get it to work with individual cells, but not when I use multiple cells like in the example above.
This Works fine:
If Len(Range("D5")) = 0 Then
MsgBox "You must enter the Impress Amount before you can save this file!"
Cancel = True
End If
I have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.
I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.
Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.
Code:
Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb
Rng.Copy
[code]....
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 modify/edit the properties of a CSV File. (Ex: Title, Subject, Author, Category, Keywords, Comments) through VBA
View 5 Replies View RelatedI have attached the file I am working on. I am attempting to create a link to an external file based on the value of cells in column A. Then I would like to simply copy the formula down, lets say in Column B, the rows and as I do the external file reference will change depending on the value within the cell in Column A. I hope that I am making sense.
I am using Windows Vista with Excel 2007. The files will all be within the same file folder, however, there are hundereds of files so I won't be able to open them all for the indirect to work.
Private Sub Workbook_Activate()
Dim user As String
If Worksheets("Setup"). Range("D6").Value = "" Then
user$ = InputBox("Hello. Please enter your name to inialize the program", "Enter Name")
Worksheets("Setup").Range("D6").Value = user
MsgBox ("Welcome " & user & ". Press 'OK' to continue on to the Main Menu.")
Else
user$ = Worksheets("Setup").Range("D6").Value
MsgBox ("Welcome back " & user & ". Press 'OK' to continue on to the Main Menu.")
End If
Exit Sub
but i would like to expand the "welcome back" msgbox to also display "the last time you were was was (eg: 2 days/3 hrs/14 min) ago" - which i presume would be calculated from the last save.
I'm looking for a way show the username of the person who last saved the spreadsheet, and the date & time it was saved. I would like to be able to ouput this data in certain cells on the worksheet. I have tried using something like:
View 2 Replies View RelatedI have an add-in called GeoDesiX. It is a geocoder/mapping tool that uses Google Maps API. It works fine, even on my machine, but I think it was built using 32-bit, while I have 64-bit Excel. When I open the add-in for the first time, an example workbook is below, it works great.
The problem is, that when I save any file with the map in it, the VBA no longer works, and an error pops up in the below code that says: "Object Reference not set to an instance of an object". This happens at the Err.Raise 65000, "GeodesiX", Result part. It looks like it just cannot find the DLL file, which is the "GeodesiX" in that error part. I was reading that it might be caused by switching between 64 bit and 32 bit, but could not figure out how to do it. I am guessing you need to put Private Function Declare PtrSafe, but I could not figure out syntax on how to get that to work. I understand that you will not be able to use these as it is an add-in, but maybe you just know what I need to do in order for the vba to call to the DLL file.
I've got a couple of formulas in a workbook which lookup values in another workbook. When I reopen the initial workbook the formulas results come back as errors (i.e. #VALUE!) unless the workbook it is looking at is also open. This is not desirable, does anyway know how I can lookup values in another workbook which are not open? The formula which is causing me problems is as follows:
= SUMIF( 'H:Infozoom[2006-01_assy.xls]New Table1'!$F:$F,$A8,'H:Infozoom[2006-01_assy.xls]New Table1'!$AH:$AH)/60
I have a file that keeps setting it's "Saved" property to False when I change something in another file. I cannot find any external links in the file.
The file used to be "Personal.xls", but I renamed it.
Macro (saved on the workbook not in the Personal macro file) which should give the following info (lets say on sheet 1, starting A1, A2) who last saved that file and the date.
View 9 Replies View RelatedI have a file with code attached and when I try to save it I am told that the file cannot be saved in this format and that I must unprotect the code before saving.
I have no OnEvent macros in the code.
Does anyone have a solution for this problem. The code must be protected, non-protection is not an option.
I have a folder with some 126 word document files.
What I need is to create list of file names in a column & its properties like Author, Date modified in adjacent columns of each file..
I have a macro that returns the time the sheet was last saved:
Function LSDate()
LSDate = Application.Caller.Parent.Parent. _
BuiltinDocumentProperties("Last Save Time").Value
End Function
Unfortuantely, the cell doesn't update without recalculating the cell. Is there a way to get the cell to update automatically every time the sheet is saved?
I have an excel file that gets updated every month but i only want one of the tabs to get saved automatically into a txt file when i click on a "finish" button or something like that.
View 4 Replies View RelatedI have a template that when it is opened it will automatically save the file to a specific location with the date in the file name.
Currently my code is
Private Sub Workbook_Open()
ActiveWorkbook.SaveAs "G:Shared"IT_New " & Format(Date, "mm-dd-yy") & ".xls", , , , , , xlShared
End Sub
Does anyone know a way to have this code disabled (either by deleting or making it all a comment) on the saved file (xls) so the speadsheet does not try and overwrite itself when I go back and open a previous days spreadsheet?
I'm not sure how I did it, but I opened up a new Excel file and accidentally saved it over a file I've been working on all afternoon. Is there any way to recover the original file (per the last time I saved it about 20 min ago)?
I looked in the "wastebasket" and only found files that I had specifically DELETED. But I didn't specifically delete this one, instead I saved over it.
I found this for my problem (creating a macro that starts when the file gets saved): Private Sub App_WorkbookBeforeSave()
I tried to use it, but the macro didn't do anything when I saved the file.
Basically I want to point my Excel sheet to a bunch of external Excel files. To get data from an specific cell (in this case, cell F10) of the external file, all I have to do is this: ...
View 9 Replies View RelatedThe problem with the Write macro was that, although it did work, it used SendKeys which has to be slowed down considerably.
Here is a new version of the Write macro that works as normal. I originally tried to use CDDBControl.dll version 1.2.0.51 which is widely available on the internet but found that I could only get it to change 1 file before crashing Excel.
I have put the READ macro in the next message.
Dim ws As Worksheet
Dim FromRow As Long
Dim LastRow As Long
Dim FilesToChange As Integer ' number of files to change
Dim FilesChanged As Integer ' number of files changed
Dim MyFilePathName As String ' full path & file name
Dim MyFileType As String ' mp3 wma etc.
'-
Dim id3 As Object
Dim MyArtist As String
Dim MyAlbum As String
Dim MyGenre As String
Dim MyTrack As String
Dim MyTitle As String
Sub WRITE_TO_EXPLORER()
Application.Calculation = xlCalculationManual
Set ws = ActiveSheet
Set id3 = CreateObject("CDDBControlRoxio.CddbID3Tag")
LastRow = ws.Range("A65536").End(xlUp).Row ' count worksheet rows
FilesToChange = ws.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Count
If FilesToChange = 0 Then MsgBox ("No files to change."): Exit Sub
FilesChanged = 0........................
I am getting error:
Run-time error '1004': Unable to set the Orientation property of the PageSetup class
I am using the following
I have this excel file that is 17 sheets long and I made a table of contents page so it is easier for people to navigate. I know my hypertext links in my file are correct because it works when I click on the hypertexted cells, my excel file jumps to the respective sheet.
However, when I save the file as a pdf the hypertext links do not work. how to get my table of contexts to jump pages in a saved pdf file?
I'm using a macro to open an other excel file to get data from it. this goes by Workbooks.Open Filename:=totalopen (totalopen =Filepath & filename earlier defined) Because the opening file can have the same filename, my question; is it possible to open the latest/newest saved file? So also look at the saving date/time of opening excel file.
View 5 Replies View RelatedI need the following sort code to run anytime the file is saved....I can't figure out what to title the sub and where to put it (ie. This workbook or in a module). I am attaching the file for reference. The file will not always be saved using the "save as" option - sometimes it is saved just by clicking the "save" icon
View 3 Replies View RelatedI have an Excell 2003 workbook with 6 worksheets and some VBA programmed macros in it.
With a small amount of data the .xls file size is 150k. Then I tested it with a much larger sample of data, which goes into one of the worksheets, resulting size 3MB. Later on I reverted to reading in the original small amount of data but the file size stubbornly sticks to 400k instead of reverting to 150k.
The old data is definitely not there anymore - I used this code to clear it:
if there is an easy way of showing the active file "Last Saved Date" on a Userform and have it show automatically when the Userform is opened? The Scenario is:
I have a "Main" userform screen and I want to have a text line which says, "File last updated on xx/yy/zzzz ". This must show up automatically when the "main" userform is opened.
I have a template excel file that has links to data in another excel file in the same directory as the template. However, I save the template with a new name in a new folder and the link no longer works. The link changes to the location with the new file and of course the file I am trying to like to is not in that location.
The link shows the full path in the template file so I would think it would keep the full path when saving to a new location but the file doesn't.
How do I make the link address static?