SaveAs Method With Filename Derived From Cell Reference

Jul 24, 2012

I am trying to use a cell reference to have excel automatically Save a worksheet using a cell reference as the workbook name. When I step through the code and use the immediate window I get the correct name for the file (fname in this case) but as soon as Excel tries to save the file, I get an error.

Here is the code I am using:

fname = Sheet3.Range("I1").Value
ChDir "S:Op CostsBudget 2013Data"
MsgBox "The actice file will be saved as " & fname
ActiveWorkbook.SaveAs filename:= _
"S:Op CostsBudget 2013Data" & fname & ".xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

View 5 Replies


ADVERTISEMENT

SaveAs Method Working In Both 2002 & 2000

Sep 19, 2007

We're gradually moving over to Excel 2007 (from 2002) at my workplace.

My problem is that I need to run macros on both versions but I'm having trouble with the SaveAs method.

I have Excel 2007 set to save automatically in Excel 97-2003 File Format but this doesn't seem to apply to files saved using VBA.

My original 2002 code is as follows:

Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:ReportsNewFile.xls"
and 2007
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:ReportsNewFile.xls", FileFormat:=xlExcel8

How can I get it to switch depending on the version it's run on? Unfortunately Excel 2002 doesn't recognise '=xlExcel8' so I don't think I can use an If/Else statement on the version number.

View 10 Replies View Related

Refer Folder/Filename In VBA Code Through Named Cell Reference

Oct 31, 2008

I have the following codes in which I am to refer a file and folder to do some further processes.

myfilename = "C:Documents and settingsacsMy DocumentsEntrymyfile1.xls"
folderPath = "C:Documents and settingsacsMy DocumentsEntry"

I want to enter these two paths in a sheet in my excel report file (For example, I am running the report from Report1.xls, in which there is a sheet name "Filepaths". In this,

in B5, I would like to enter the Filepath (B5 named as "FILEPATH")
in B6, I would like to enter the Folderpath (B6 named as "FOLDERPATH")

So that, if I am copying the folder to another area, I no need to change the code every time in the VBA editor. I can do changes in these cells and it will be taken as the path to run the code further.

View 2 Replies View Related

Variable In Filename Reference

Apr 17, 2007

I'm working on a spreadsheet that will access two weeks worth of data at a time. Is it possible to have the filename reference contain a variable or a link to another cell in it?

For example: ...

View 6 Replies View Related

Possible To Have A Sheet Name Derived From A Cell?

Nov 12, 2009

Is it possible to have a sheet name derived from a cell?

I am creating a workbook where the users will be able to edit a list of names, and add certain data to each person. There will then be a separate sheet for each person that gives analysis if their individual data. The user will change the names in the list as people come and go, and want the sheet names to match.

View 9 Replies View Related

How To Use VLOOKUP For Cell Value Which Is Derived By Formula

Mar 6, 2014

I'm wanting to lookup the content of a cell, which is a string, "Name", resulting from a formula within that cell.

For example the first code is referencing the cell output from the second formula.

Code:
=VLOOKUP(N2,RandomTab!$A$2:$E$60,5,FALSE)

where N2 is

Code:
=UPPER(VLOOKUP(B2,OtherTab!$A$1:$H$68,8,FALSE))

presently it, the first VLOOKUP, returns #N/A

View 3 Replies View Related

How To Replace Workbook Reference Filename On A Formula

Apr 6, 2014

I have to many refrence links I need a macro that replace the filename. How to replace workbook refrence filename on a formula using cell value,

example:

from [13.xlm]2014.Goals'!C8 to ["cell-value".xlm]2014.Goals'!C8

View 1 Replies View Related

Cell Retains Value Derived From Dynamic Function.

Jan 14, 2009

Let's say I have three cells; A1, B1, C1. A1 = a random number (RandBetween 0 and 100) B1 = If(A1>99,1,0). I want to increment C1 everytime I refresh the randbetween via f9 so that the first time A1 returns a value of over 99, C1 picks up a value of 1 from B1 and then the next time I get a value of A1 over 99, C1 adds the value of 1 from B1 to its existing total, giving a total of 2 etc etc etc.

Essentially, what I want is: If A1>99,C1=C1+1. I can't work out how to get C1 to hold its value when I refresh the sheet. I realise it's probably pretty simple, but it would appear that so am I.

View 2 Replies View Related

Cell Addresses Derived From Concatenated Values In Separate Cells

Apr 2, 2013

I need to return a value from a cell in Column H, but with a row number that varies with each entry.

I repeat this formula every 5 rows or so. But the cells referenced on another spreadsheet are only one row apart. Because of this discrepance, when I copy my highly developed cluster of formulas, they only reference every 5th entry on the other sheet. What I've been doing is manually altering long, complicated formulas by hand. I can't do a replace function because every once in awhile it'll find an extra instance of the digit I'm replacing and mess up parts of the formula I don't want changed. I could avoid this tedious, time-consuming work-around if I could make a formula that would do something like this:

=Display Value from in column H: (row number per cell B1)

And cell B1 would include the number 3789.

So the returned result is the value from cell H:3789

View 4 Replies View Related

Amending The Filename In Cell("filename")

Jul 7, 2006

I am trying to display the Filename in a cell on my worksheet. However, when I enter the =Cell("filename"), it provides the full path (C:/Documents......Cost Summary.xls) How can I display only the filename without the path?

View 3 Replies View Related

Force SaveAs As Cell Text & Date

Jun 7, 2007

I have a customised excel woorkbook that will be distributed to several locations with different computere systems, etc. I wanted to keep some convention with the naming, so I've put in a saveas routine to open the saveas dialog and change the default name. The user can then select the directory location and save the file.

Here's the
Sub Saveas_routine()
'
Dim strName As String

On Error Goto InvalidName
strName = "E-RAMP " & Sheet1.Range("A1") & Format(Now, " dd-mmm-yyyy") & ".xls"
Application.GetSaveAsFilename (strName)
Exit Sub

InvalidName: MsgBox "The text: " & strName & " is not a valid file name.", vbCritical, "E-RAMP"
End Sub

The problem is, when the save button is pressed in the saveas dialog, the dialog disappears and the the workbook doesn't save.

View 9 Replies View Related

Right Axis Derived From Values?

Feb 28, 2014

The goal of this chart is to show how sales are adding up relative to bonus levels for the month. The problem for me is in getting the right axis tick marks, labels and chart lines to match the values in Columns C and D. This need may be further complicated because the target amounts increase by a fixed percentage (11% in this sample, but it can vary).

View 4 Replies View Related

Conditional Sum Derived From A Date Interval

Feb 21, 2010

I'm trying to sum each months visa purchases. The data is in three columns: date,value and payment type.

I can't get this formula to work:-

{=SUM(IF(AND(Purchases!C6:C999>DATE(2009,12,28),Purchases!C6:C999<DATE(2010,1,29)),IF(Purchases!K6:K 999="Visa",Purchases!H3:H999)))}


It's returning a value of 0 (and if that were true I'd be delighted!)

View 12 Replies View Related

Hiding Formulated Values Derived From 0 Value Cells..

Dec 7, 2009

I have a sheet with cells that will vary and change in value. The far right column (in this case G) is calculating a percentage from cells in columns D and F. At the moment only 9 rows are being used but I have made provision for the list to extend down to row 100 for future use. Cells in columns D and F are receiving their values from sumif formulas on another sheet, therefore a lot of cells are showing a 0 value for the moment (which I have formatted to show blank).

My problem is that I have cells in column G (percent formula) which are of course showing #DIV/0! due to the fact that they are calculating cells with 0 value. How can I format the cells in column G to show blank until they receive a real calculation!

View 2 Replies View Related

Automation Of Solver: Must Inputs Be Derived From Cells?

Jan 20, 2010

I'm trying to automate the solver using a vb program. I have found this example which is just about spot on for my needs:

View 4 Replies View Related

Reverse Pivot Table :: Recreate The List It Was Derived From

May 14, 2009

I am trying, starting from a list that has the same structure as a Pivot Table, to recreate the list it was derived from.

I added a sample to show what I mean.

View 8 Replies View Related

Evaluate Formula Derived Empty String In SUMPRODUCT?

Dec 10, 2013

I've been trying to get this SumProduct formula to return anything except an error for a while now.

=SUMPRODUCT(('Main Log'!$E$13:$E$17>=$C$2)*('Main Log'!$I$13:$I$17

View 2 Replies View Related

Filename As Cell Value

Oct 11, 2009

Is there a way to take filename and place value in a cell

View 9 Replies View Related

Error 'Method Range Of Object Global Failed' On FindNext Method

Dec 10, 2008

I'm trying to get the Find and FindNext methods to work. Column C contains serial numbers and there's a chance that a serial number might appear more than once in the column. What I'm trying to do is get Excel to find the first occurance of the serial number, find what row it's on and then see if this matches the variable 'CurRowNo' (defined earlier in the code). If it doesn't I want it to look at the other occurances of the serial number, find what row they're on and see again if it matches CurRowNo.

The variable 'EngCount is the number of occurances of the serial number (also worked out earlier in the code). I've got the code below, but I get the error 'Method Range of Object Global Failed' on the FindNext line. I have no idea what this error means or why it's happening.

View 3 Replies View Related

Alternatives To CELL (filename)

Apr 7, 2009

CELL("filename") gives you the name of the current workbook but if you switch to another workbook and then come back, the name has switched to the other workbook. You have to refresh to make the name switch back to that of the workbook you used the formula in.

What alternatives exist, short of writing some VBA, which is an option, to fetching the name of the workbook via a formula and having it remain the name of that workbook when you go away and come back again?

View 2 Replies View Related

Filename = Cell Contents

Dec 11, 2008

I would like to modify the below so that the contents of cell D2 of the current file becomes the file name.

View 2 Replies View Related

Change Filename To A Name In A Cell

Jan 3, 2010

GOAL: Have an Excel file renamed automatically using the contents of a cell in the first sheet. DETAILS: The cell with the new file name is always D8. The cell is blank initially (although it doesn't have to be). OTHER INFO: OS= WIN 7 Excel= 2007
EXAMPLE: If I input "Mike T" into cell D8, the name of the file will be Mike T.xls

View 5 Replies View Related

Insert Filename From A Cell?

Dec 10, 2013

Is it possible to insert the filename of another excel file based on the information in a cell within your current file?

I have a file named 12-07.xlsx, which has a sheet Data 12-07, and that sheet has information all through it. I want to populate a cell in a new file with inform from the 12-07.xlsx cell B3.

I know the Formula is ='[12-07.xlsx]Data 12-07'!$B$3

Is there a way that I can use a cell within the current file, say cell B1 (which has a value of 12-07 - which is the same as the file I want) to populate the filename? Something like

='["=B1".xlxs]Data 12-07'!$B$3 (whereas "=B1" would be 12-07)

I know that will not work as is, but is this concept doable?

View 3 Replies View Related

Filename As Cell Contents

Sep 19, 2006

How can I have the filename stored in a certain cell of the worksheet?

View 4 Replies View Related

Saving Filename From Cell Text

Nov 18, 2009

When the user goes to File, Save As, I would like the filename to be populated from the contents of cells B2 and B3, i.e. 11/18/09-23. Is this possible? Also in the same code, can I specify that I want the destination folder to be K:Sheets?

View 11 Replies View Related

How To Print Sheet1 Into PDF With Filename Given In Cell A1

Jul 15, 2009

How to print sheet1 into a PDF file with the file-name given in cell A1.

View 9 Replies View Related

Saving Filename From Cell Value In Sheet

Dec 2, 2011

I would like code to automatically save my spreadsheet with the name that is present in cell F4 of the activesheet. For example, I would like to save my file to desktop and cell F4 contains the text 'hitec 348'

View 5 Replies View Related

Linking To Workbooks Whose Filename Is In A Cell

May 9, 2006

One folder on the drive contains excel files, which are created daily from a template. I use these files, the 20 most recent days' worth, to create an analysis file. This new file is organized in rows, starting with the date. The files I am using are all saved in the form "Stats yyyy mm-dd.xls" The first line of each row of the analysis spreadsheet starts with the date, in the format "yyyy mm-dd". Is it possible to create a macro, or in another way, for the analysis spreadsheet to automate the reading-in of data from these different files?

View 2 Replies View Related

Cell Function With FileName Changes Sheet Name

Dec 2, 2007

Excell 2003

I put = cell("filename") in cell A1 on every sheet to display the full pathname of that sheet.

it seems that the path does not always display the correct sheet name- rather it seems
that when changing from one sheet to another the path is not always updated.

Is there a switch that needs to be activated to ensure that the path is updated correctly when changing sheets on the same worksheet?

View 3 Replies View Related

Change Formula Cell Reference Based On Another Cell Reference

Jan 8, 2012

How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.

________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)

If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved