Referencing File Name While Changing File Name
Jun 8, 2007
I have a more general question. When doing something such as loading a picture, is it possible to be able to have code which the loading file name changes with a variable. For example, if I have pictures with names 1.jpg thru 120.jpg(all in the same directory) is it possible to write a code that loops and loads the proper image without having to write a section of code for each file name? This would be the directory(C:Documents and Settings muzaDesktopNew DatabaseDatabase Images312312_250) so could I code the filename calls something like this with having picname be the variable which goes from 1 to 180:
.Pictures.Insert( _
"C:Documents and Settings muzaDesktopNew DatabaseDatabase Images312312_250" & picname & ".jpg").Select
View 2 Replies
ADVERTISEMENT
Mar 21, 2013
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
View 5 Replies
View Related
Jun 12, 2007
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 Related
Feb 19, 2014
I have a blank file and a data file for current year with formulas.
So I am trying to take a couple of tabs from current year data file and pasting them into the blank file.
My question when is do that all the formulas get referenced to the current years file. i want to keep the same formulas but reference the tabs on blank file.
Is there a quicker way to do this rather and going into all the cells and changing reference from current years files data to blank files data.
View 3 Replies
View Related
Oct 21, 2009
I am working on an excel dashboard to display life information about energy consumption.
there is currently a bespoke syatem which does this but we need the dashboard to be changed so the job has fallen to me.
Energy usage across the site is recorded in a spreadsheet (automatically) every half an hour and is saved in a folder named CSV data, however the files are saved in a format which i assume is something for the current package: *.ew7
I can open and view the data by clicking "open as" and selecting excel.
I can reference to this data from another excel spreadsheet (dashboard) whilst they are both open it will update.
However when i close all spreadsheets and open just the dashboard it tells me that the links cannot be updated.
I need a solution or a way around this so i can reference from one workbook to another.
Has anyone had any experience of this kind of thing? can anyone offer a humble analyst a bit of advice?
I was not able to attach the file here (as its in the ew7 format) but i have uploaded it to rapidshare for anyone who wants to take a look...
View 14 Replies
View Related
Oct 30, 2008
I have this Macro for Excel which imports 1 file C:datafilesuser1-data-1.txt into cell E52:
View 6 Replies
View Related
Aug 13, 2012
How to copy a formula (16 columns) from a workbook to another, without referencing the source workbook.
Some people are using tricks such as replacing the equal sign "=" for another character such as "^" then using replace all to put the equal sign back again... but this is too much trouble.
View 2 Replies
View Related
Aug 13, 2008
I have a file created in Excel 2003 that uses a vlookup to reference another file, also created in Excel 2003. The function returns VALUE when the referenced file is closed. I get the VALUE error whether or not I update links upon opening the file. If I open the source file, the function calculates properly.
Here is the formula when the referenced file is open:
=VLOOKUP(A38,'[June Scorecard confirmation.xls]VPSC Summary'!$C$10:$F$22,4,FALSE)
The full & correct path appears in the formula when the referenced file is closed.
Columns C, D & E in the referenced range contain text, column F contains a Sum. This file is linked to another file, also using lookups. The linked area in this file works without problem, but it is a precedent to the calculation in column F.
Both these files reside on a network in subdirectories of the same logical drive. I am using a laptop that briefly had Excel 2007 installed on it, but was wiped clean before it was issued to me.
View 3 Replies
View Related
Oct 10, 2011
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 Related
May 3, 2007
I have a quoting application for damaged vehicles, this application is able to export all quotes and information relating to quotes into an excel sheet. the export file arranges the jobs in rows with each value in a separate cell i.e.
Job #|| Name || number || Parts $ || Total $
1453 || Mike K || 1234567 || 123.00 || 222.00
1533 || John C || 4353456 || 234.00 || 2342.00
Etc
Each job is listed on a separate row as it exports the records from an access database. once the information is exported I need to open another workbook and type in a job number into a cell and have excel automatically reference the relevant job and information relating to that job (all info in the same row) and fill in all the details from the other workbook into the new workbook. Example: the exported file contains 200 jobs, of those 200 i require 20 to view for one week. I have the job numbers of the jobs which I need to get information for. i open up another workbook and type in the 20 job numbers in separate rows and excel fills in the rest by referencing the other worksheet.
View 4 Replies
View Related
Dec 13, 2013
I have an Excel file that contains formulas that reference external workbooks on a shared network. Each month, I copy the column of formulas over to the next month's column. Then, to update the file path, I highlight the new column and do a Find-and-Replace, swapping the previous month's name for the current month. This practice works fine. The only thing is ... the "Open File" promptbox appears for each formula where I updated the file path (i.e. and this can be hundreds instances!). I wind-up having to choose the file from the exact same file path. It is an unnecessary, and annoying extra step to take.
View 2 Replies
View Related
Nov 18, 2009
VBA Changing file name
I'm currently using this code found on Mr Excel
View 5 Replies
View Related
Nov 30, 2009
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.
View 4 Replies
View Related
Oct 30, 2012
I am trying to use code in a VBA macro to change my file to a read-only file at the end of the macro after I've done a few other things to the file. I'm using this line of code:
ActiveWorkbook.ChangeFileAccess xlReadOnly
This code does in fact change the file to read-only. After the macro finishes I see "Read-Only" in the title of the file. However, when I close the file and re-open it the file is no longer read only.
I've tried changing the code to this:
ActiveWorkbook.ChangeFileAccess xlReadOnly
ActiveWorkbook.Save
But that doesn't work because it tells me I can't save the file after it's been converted to read-only.
View 1 Replies
View Related
Jun 6, 2007
I am a file that I want to open with VBA, however, the name of the file is refreshed everyday and the name change everyday as well. (Ex. on 5Jun, I would want the file named ABC0406.out, and on 6Jun, the file I would want would be ABC0506.out)
Noticed that the "ABC" and the ".out" do not change. It's just the date is one "weekday" before. I've tried the "dateserial" formula but can't get it to work.
View 4 Replies
View Related
Mar 25, 2009
my formula is as follows: =(VLOOKUP(A7,'[Monthly AR Analysis - Feb09.xls]AR - Current'!$A$1:$I$153,8,FALSE)). I will be using this for formula on about 15 different worksheets ... my issue is the file name changes every month and currently I have to go in and do a find and replace, I would like to have one main page where I could change the file name month and it will change all the rest.
ie: Monthly AR Analysis - Feb09.xls next month changes to Monthly AR Analysis - Mar09.xls .... So basically each month I want it to pick up the new file that has been saved with the new name. So what I would want is to be able to have a cell that I could change the Feb09 to Mar09 and then it would do the same to all the worksheets. I attempted to use CONCATENATE to change file name and it worked but will not work within the Vlookup.
View 4 Replies
View Related
Mar 20, 2014
I've got a macro set up to copy a range of cells in one workbook, open another existing workbook, and then paste that range. So far so good.
However, I need the macro to also save a copy of that second workbook with a filename that is derived from the original workbook.
To be a bit clearer, let me give an example of how I'd like this to work. Let's say the original workbook is titled Pickle2014-03-14.Raw.xlsm, and I have a second workbook titled UploadFormat.xlsx. I want to run the macro in Pickle2014-03-14.Raw.xlsm, have the data pasted to UploadFormat.xlsx, then save UploadFormat.xlsx with the file name Pickle2014-03-14.Final.xlsx.
View 2 Replies
View Related
Jul 20, 2009
I am running the macro below which changes details in my spreadsheet using details from an external table.
Range("C7:E36").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=18
Range("C42:E51").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=18
Range("C58:E69").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=15
Range("C75:E86").Select
If I want to perform this process without having to change the directory to the t drive etc, how do I change the macro? Is there a way, that as long as each of the relevant sheets are in the same directory, i can point the macro there? Ie, as long as all saved together in My Documents or Desktop, the macro automatically looks there?
View 9 Replies
View Related
Jun 15, 2006
I have Workbook1 that I need to update from another Workbook2 in a different location on the network. Workbook2 is read only. Workbook2 is generated automatically with a name like "product 06-14.xls". Now my problem is that, since it get created automatically, the next day it will create a new workbook with a new name "product 06-15.xls" leaving the old file in the same directory for future reference.
I need to update a cell with the new current date up to the completion of the month plus the beginning of the next month to make it a complete month, so a completed month will be from 05-01-2006 to 06-1-2006 because the last day of the month doesn't get recorded until next day. What I have problems with is the looping to go to the next day until the beginning of the next. Then stop and start again on a new cell inputting the new month.
April-06 ---- 5,907
May-06 ----- 4,954
June-06 ------ 524
July-06 ----
This is what I have so far.
[PHP]
Sub GetData()
Dim Cel As Range
Dim Row As Long
Dim Col As Long
'Turn off ScreenUpdating for faster macro runtime so screen won't flash while running.................
View 4 Replies
View Related
Aug 17, 2006
In VBA, how do open 2 new windows, and allow for the fact the file name will change (or name the new windows the current file name)? My current file name is "Metal.xls"
Here is what the code looks like now:
Windows("Metal.xls:2").Activate
Windows("Metal.xls:1").Activate
View 3 Replies
View Related
May 5, 2007
I am trying to find a way to dynamicly change CSV files .What I want is to add another column to the table with the values that based on the pre programmed parameters. For example if i have A,B,C,D columns and rows 1,2,3,4,5,6,7 I need macros to add values in column E based on the values of column B(for example)
I want to be able to pre-set those algorithms.For example for value 1033 in column B I need macros to insert value 1.25 in column E in the same row,for value 951 in column B I need 0.3 in column E in the same row. Is it doable just in Excel?
View 3 Replies
View Related
Feb 26, 2008
explain or point me the right direction for the problem below: I need to find a way to vlookup a spreadsheet that has changing dates in the file name such as filename_xx.xx.xx. This is a piece to a macro I'm currently working on.
View 2 Replies
View Related
Feb 4, 2014
Can I use a vlookup combined with IF function to lookup up to a different file dependent on what the cell says.
For Example cell a1 could have the value Alan Smith, Tony Brown or John Doe dependent on what value is entered the lookup would "lookup" against the file named Alan Smith, Tony Brown or John Doe.
View 6 Replies
View Related
Nov 20, 2013
In any other software, when I save a copy, or export to a different format the original file remains unaffected but in an excel it seems to work differently...and I end up having to save my file back to the xslx format. this extra time wasted when I am regularly exporting but it also makes me a bit nervous that I might accidentally not save back to the format with all my worksheets etc etc..
View 1 Replies
View Related
May 22, 2008
I want to convert the excel file into text document with out changing it
file type
for example
in the wordpad i have the text document in this type
"Candy","1253563"
I want this to be open in excel and i can open in the excel using delimited option but i want only to change the number "1253563" and i can use autoofilter to filter and i want to change the number "1253693" in the excel and i want that excel should open in the format of text document without any change as i mentioned before
it should open in this format
"Candy","1253693" only the number should change and this action is repeated for many numbers
View 9 Replies
View Related
Nov 23, 2009
Using VBA, can anyone please advise if it is possible to change the name of a large number of files in a directory. I want to get each file name as a string, truncate the first few characters, then change the name to the new filename, all without opening any of the files whose names are to be changed.
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
Jun 6, 2005
When opening a .CSV created from a third party software, one of the columns is converted to date format. The numbers are imported in #/# format. and Excel changes most of the numbers to 2-Jan, 3-Jan etc... This would not be that big of an issue if I could simply set it back. Clearing format or changing to text converts it to a 5 digit number.
The only way I have found to get it to work properly thus far is to open a blank sheet and import data and setting the column to text prior to import.
I would like to find a way of opening the CSV file without all the extra steps. Is there a setting that can be turned off?
View 3 Replies
View Related
Dec 18, 2012
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
View 1 Replies
View Related
Jul 6, 2012
I am trying to open a website, then for excel to download the csv file, then for it to save it in a specific folder under a specific name and file format (excel).
I am successful at opening the website with the following code, but how to do the rest.
Sub Searchez()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://quote.morningstar.ca/Quicktakes/stock/keyratios.aspx?t=clwr®ion=USA&culture=en-CA&ops=clear" 'load web page google.com
IE.Visible = True
While IE.Busy
DoEvents
Wend
'IE.Navigate2 "javascript:SRT_keystuts.exportcsv()"
'this is the name of the download link as from when i hover my pointer over download link.
End Sub
View 1 Replies
View Related
