Excel 2010 :: Macro To Open XLSM File Based On Latest Date Found In Filename
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
ADVERTISEMENT
Feb 22, 2013
I have an excel 2007 file which contain a lot of macros. File size is 100MB. This file is not developed by myself, it's post in the webpage and let the members of the page to download on daily basis.. I've been downloading and using this file for over a year with no problem until this week.
Problem: During opening the file. There is a message box popped up, saying "Excel found unreadable content in filename.xlsm'. do you want to recover the contents of this workbook ? If you trust the source of this workbook, click Yes." When I click "yes", excel is trying to repair the file. Once it's done, I could see only the contents, however, all the macros are gone. When I copied this very same file and open in other notebooks, everything is working fine...
Assumption: Couple days ago, I was writing/testing macros in other workbooks and also opened this particular file. I might did something stupid which cause this problem...
During the same time, I used to have the same problem with another file. I solved it by copy and open with another PC then copy macros and paste it to the file on my notebook.
View 6 Replies
View Related
Sep 13, 2012
I am using the macro below to save a file. It works with the extension .xlsx but not xlsm. I get a message that I can't use this extension for this file type. I am missing something fundamental.
Using Excel 2010
Sub testsave()'
Dim a As String
Dim b As String
Dim c As String
Dim d As String
b = "Myyfile"
c = b & ".xlsm"
a = ThisWorkbook.Path
d = a & "" & c
ActiveWorkbook.SaveAs Filename:=d
End Sub
FYI - there are no macros in the file I am trying to save.
View 1 Replies
View Related
Nov 17, 2011
I am working with Excel 2010.
There are certain .xlsm files in our system (some have macros and others don't) which generate "Error: Source not Found" in the Edit Links dialog box when the Check Status button is pushed. However, the source location is correct, as you can click on Open Source and it will open the correct file.
For simplicity of explaining my issue, consider the following scenario. In my workbook "Corn Production Summary.xls_" I link to 2 source files: "Iowa.xlsm" and "Nebraska.xlsx" (Note: The extension of the summary file doesn't matter. This error happens regardless of what the summary is.)
When I open "Corn Production Summary" I am prompted to update values. I select Update and the file refreshes and recalculates appearing to have updated all values (i.e. no error messages). However, the values from Iowa.xlsm did NOT update and there was no error message indicating that the values didn't update.
To confirm this, I go to Data>Edit Links>Check Status in "Corn Production Summary" and I see that Iowa.xlsm has the "Error: Source not Found" error. I click on Open Source and once the file is open, my data in the summary file will update.
One work around was to do the following process (with the summary file closed):
1) Iowa.xlsm and Save As Iowa.xlsx (removing macros if needed)
2) Save As Iowa.xlsx as Iowa.xlsm (overwriting the file that is currently there and readding the macros)
3) Open the summary file and both Iowa and Nebraska work just fine now.
There are a lot of files like this, and because Excel doesn't prompt us that it isn't really getting the values from these certain .xlsm files we would have to search in "Edit Links" for each file to make sure that they really are updating.
View 3 Replies
View Related
May 9, 2013
I am trying to create a macro using the recorder that will select the latest date in a drop down list. The dates upate each day in the pivot table after the table is earlier refreshed. Using the recorder simply picks the same date each time. I want it to select the latest date. Am using Excel 2010 and have some very limited experience in vba for editing macros.
View 1 Replies
View Related
Aug 1, 2013
I'm trying to save an Excel file to be opened on Excel 2003, but with some VBA formulas. What is the compatible format? 2003 doesn't open in .xlsm format.
View 1 Replies
View Related
Mar 22, 2012
Excel 2010, Win 7. I have attached a .xlsm file to an email. The contained macros run perfectly, and one of them is "public" so it shows up in "Developer - Macros", and "Customize Quick Access Toolbar" lists.
If the recipient clicks and drags the attachment to a folder, or copy/pastes the attachment, it works fine. (Macros are enabled, etc.)
However, if the recipient right clicks on the email attachment, and selects "SAVE AS" from the item list, the file saves, has the right name and extension, looks ok (the icon has the exclamation point, etc), and it is the correct size, but it simply will not load. You can double click, do a file open, etc. but it will not load. It's a hidden workbook, but if you "open it" and Alt-F11 to show the VBA editor, it isn't there!
It's not stopping the project - we simply tell them to click/drag, etc. but I totally do NOT understand why that happens.
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
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
View Related
Jan 5, 2014
I am an extreme novice to using macros.
I used the 'record macro' feature to create the following: I created a rectangle box on sheet1 of NEW TEST.xlsm to link to the macros incidated below. Niether macro moves on after reaching the Workbooks.Open statement.
I am at a loss. Is there something in Excel that needs to be set? Is there something else that I need to do version of Excel is 14.0.6129.5000 (32 bit) MS Office Professional Plus 2010
What I am trying to accomplish is this: From 'NEW TEST.xlsm', open an excel file on the network, add a new sheet to this network file, calling it 'Pivot'. Then create a pivot table in this new sheet from the data in sheet data1 of this same network file. Then move control of macro / processing back to the NEW TEST file. Eventually I will do more, but for starters, I cannot even get the macro to add / name a new sheet in the network file.
[SIZE=3][COLOR=#000000][FONT=Arial]PS I have attached the NEW TEST.xlsm fikle as we as the data file. NOTE: the data file has been reduced to one day's worth of data, to to the upload file size.Also, my Excel Trustred locations has "Allow trusted locations on my network" checked, and the network location to the data file in trusted user locations.
VB:
Sub Open_Add_Sheet()
Dim filename, filelocation As String
Dim wb As Workbook
filename = "11 Nov, 2013 GDO AHS Agent Productivity Report.xls"
filelocation = "R:acharukAHS ReportsGDOGDO CSR ACTIVITY Report2013" & filename
Set wb = Workbooks.Open(filelocation) '
' Open_Add_Sheet Macro
[Code]....
View 9 Replies
View Related
Mar 5, 2013
I am using excel 2010 on windows 8. I load a file which has been sent to me via e-mail and save it with an upgraded name, I then do a large amount of data input and save the file again, I do this regularly and even test load it at times to check it has saved, finally I save and close the file and then on reloading I discover the version I have is the first one I saved! all the work I have done on it has disappeared! I also sometimes find random .tmp files with non-sensical names in the folder to which I have saved, but I do not have the rights to open these .tmp files.
View 2 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 16, 2014
I made the excel file with the VBA source code, saved it, opened it many times, and all at sudden I cannot open it anymore. When I try to open the file I get the following screen:
actually, Excel asks me if I want to enable macros or not, but this window is different than usual window:
when opened file with macros works fine.
So, in my case I click the Enable Macros button of the first picture and my file seems to open in some hidden instance, because the file name isn't writen in the title of the Excel window.
When I close the Excel window it asks me if I want to save changes to my file, and that proves that it was opened in some hidden instance of the Excel.
I have to say that I was not playing with the security options of hiding any part of the file that I want to open, and which doesn't open properly.
View 1 Replies
View Related
Mar 11, 2014
am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
View 6 Replies
View Related
Nov 13, 2013
I have a raw data in one file and then another file contains pivot table linked to the raw data. In the dashboard i need to show last 6 weeks trend which is linked to the pivot table. Currently i am manually unselecting previous week and selecting the latest week.
Ex: This week i select weeks 5, 6, 7, 8, 9, 10 from the pivot for the dashboard. When new data is updated next week, i want to select 6, 7, 8, 9, 10, 11. I cannot delete as i need the historical data.
I am using Microsoft Excel 2010.
View 1 Replies
View Related
Aug 6, 2014
I have an MS Excel 2010 xlms file which is password protected. Unfortunately, the person who has the password has left the company!
Which is the best tool to remove the password / recover the file?
View 2 Replies
View Related
Apr 22, 2014
I am using the following code to open a woorkbook:
Workbooks.Open Filename:= "C:UsersDesktopExcel.xlsx"
I am running into an issue were a file may be saved as .xls or .xlsm instead, but I still need to have the macro open it. Is there a way to adjust the above code, or other code for that matter, that will allow my macro to open any of these extensions?
View 3 Replies
View Related
Feb 25, 2014
I have a macro enabled file with some functions. Now I want to create a fillable pdf file from this file.
I am able to create a pdf file from Excel 2007, but not able to make a fillable one
View 1 Replies
View Related
Oct 22, 2013
I have a macro-enabled template file in Excel 2007. I would like the user to be unable to save in any format other than macro-enabled. They should be free to choose a path and filename, but not the file type.
I presume this means some VBA code in the before save event, but I don't know what.
View 2 Replies
View Related
Jun 19, 2014
I need a formula to do a partial text match on column B to find all rows that contain "825-CL-A", then sum column C for all applicable rows with the latest date. In this example the result should be "4.25 + 6.50 = 10.75". I'm using Excel 2003 for this project.
A B C
7/1/2012 0:00825-CL-A-41091-REG4.00
7/1/2012 0:00825-CL-A-41091-REG6.25
7/1/2013 0:00825-CL-A-41456-REG4.25
7/1/2013 0:00825-CL-A-41456-REG6.50
1/1/2014 0:00825-CL-A-41640-REG4.25
1/1/2014 0:00825-CL-A-41640-REG6.50
3/1/2014 0:00825-CL-E-41699-REG3.00
3/1/2014 0:00825-CL-E-41699-REG4.00
View 14 Replies
View Related
Jan 28, 2013
I am looking for a vba to use a button to save a file using the cell as its name and save the file in xlsm format. I am using Excel 2010.
View 12 Replies
View Related
Jan 30, 2014
I have emails come in daily to my outlook 2010 as an excel attachment, is there a way to open these attachement in excel by running a macro?
View 4 Replies
View Related
Aug 30, 2012
I Have a CheckBox Thats Linked with CA22
In Cell CB22 I have the Following Formula:
=IF(CA22=TRUE,"1","")
In Cell CC22 I Have...
=IF(CB22="1","RM-4E ADD1",HYPERLINK('RM-4E (Add - 1) (2)'!A1,"RM-4E ADD1"))
The Sheet namne its supposed to Hyperlink to is called
RM-4E (Add - 1) (2)
When the Check Box is Unchecked it Changes CA22 to False and Puts in CC22 Text: RM-4E ADD1
When the Check Box is Checked it CHanges CA22 to TRUE and is supposed to create a Hyperlink to that sheet...Only when i click it it comes back with
'Cannot open the specified file."
I even went as far as typing out the Word Hyperlink( clicked the sheet and selected Cell A1 on that sheet but it still comes back with the error!
Excel 2010
View 9 Replies
View Related
Nov 26, 2012
I just got a new computer and upgraded to Excel 2010 and Windows 7. When I try to open a workbook in Excel 2010, my saved file paths on the left side of the screen are gone. I want to put them back in there.
In Excel 2007 running Windows XP, I would just right click in the left side menu and click "add" and now that's no longer an option.
View 1 Replies
View Related
Dec 11, 2013
Two months ago our employer seriously limited the size of our mailboxes. We are "forced" to store emails as msg files on our network. PST archives are not allowed. This naturally sucks, as in Explorer you only have the file names and dates to look for that one particular email amongst hundreds (thousands) of msg files. I was able to compose a program in Excel 2010 that generates a list of msg files from a selected folder, together with metadata (sender, receiver, etc.) and so on.
In a test the program worked fine with 6,710 msg files I could post the code here, but the comments and many of the variable names are in Dutch (I never expected it to work in the first place). There is one thing I would like to add to the program. Sometimes I want to add an email to a new email as an attachment. Nowadays I have to look for the right msg file in Explorer by hand, which is a pitty, because I have the paths and filenames in my worksheet. I can put that in a string variable and let VBA do the looking up.
But I don't know how. I would like to be able to let VBA look for the file in Explorer, select it and then stop. I could then drag and drop the selected msg file in my new email. But all my attempts to find example code end up with code that enables a user to select a file and immediately open it. That is one step too far for me. If the msg file is opened, I cannot add it to a new email as an attachment anymore.
View 7 Replies
View Related
Apr 22, 2013
Is there a way to open a text file from Excel 2010 and specify that I want it in .xls format?
I am working in compatibility mode, and expected that when I opened a text file from code within an xls file, the text file would have 65,536 rows, but it has 1,048,576. This causes a problem when we try to copy the sheet with the data from the text file, and insert the sheet into our xls workbook. See code below. The error is: Run-time error '1004': Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook..
I know I can get the data other ways (such as copying and pasting only the cells containing data) but I was hoping to make minimal changes to the code below as I will have to make it across several templates. Specifically, I was hoping that there was a qualifier I could add to the Workbooks.OpenText statement after "Tab:=True" - Perhaps something about opening the text file in File Format 56. However I have not been able to find out how to do that.
Code:
.....FileToOpen = Application.GetOpenFilename("All Files (*.*),*.*,GeoTAC Files (*.ctf),*.ctf, _
Text Files (*.txt),*.txt,Excel Files (*.xls),*.xls", 1, , , False)
Workbooks.OpenText Filename:=FileToOpen, _
[Code].....
View 3 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
May 6, 2014
I would like a macro to be able to save 26 tabs within the one document to individual PDFs.Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.I would like each PDF to automatically be named with the value in cell E10 of each tab.E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.
I am using Excel 2010.
View 5 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
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
