Saving A File Using Cell Reference
Nov 8, 2009
I'm trying to open a workbook using a macro, then saving the workbook that's just been opened using a cell value as the title.
This is what I have so far ... to open up the workbook and then run a macro from there .. this works fine
Sub Test1()
'
' Test1 Macro
' Macro recorded 08/11/2009 by Tracy
'
Workbooks.Open ThisWorkbook.Path & "/MultiFuel Expense.xls"
Application.Run "'MultiFuel Expense.xls'!TestSave"
End Sub
This macro is the one I'm struggling with. I need to save a copy of the workbook I just opened (currently named MultiFuel Expense) in the same directory using the cell contents of G2 as it's name with _expense on the end.
Sub TestSave()
'
' TestSave Macro
' Macro recorded 08/11/2009 by Tracy
'
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "" & Range("G2") & "_expense"
End Sub
View 9 Replies
ADVERTISEMENT
Dec 12, 2012
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]....
View 4 Replies
View Related
Dec 12, 2006
I am trying to save this file but I'm having problems with "my Name" This information is in a workbook that is already opened called "Recaps" and the sheetname is "Data" and the value I am looking for is in "$C$5". I open another workbook called "Spare" and want to rename and save it to the code below.
Dim myPath$, myName$
Application.DisplayAlerts = False
myPath = "C:Service RecapsJohn Mudaro"
myName = Workbook ("Recaps) Sheets("Data").Range("C5").Value
ActiveWorkbook.SaveAs myPath & myName
Application.DisplayAlerts = True
View 2 Replies
View Related
Jul 16, 2014
I am trying to find a code that will allow me to Save Rows to a txt file with cell separator ?
I have put together this from other peoples work online and now all i need to work out is how to add a cell separator like a comer?
View 4 Replies
View Related
Nov 13, 2012
I'm actually using a macro that saves all the sheets from a workbook in new separate files, and save them in a specified folder.
Code:
Sub Estrazione_Schede()
Dim n As Long
Dim myNome As String
Dim myPath As String
myPath = "D:path"
[Code] ......
What I'm trying to do is that, instead of saving all the sheets in one folder, the macro would save them in different folders, depending on specific values in a specific cell of each sheet. I tried to modify the macro like this, but it doesn't seem to work properly:
Code:
Dim n As Long
Dim myNome As String
Dim myPathOne As String
Dim myPathTwo As String
myPathOne = "D:pathOne"
[Code] .....
With this code the macro ends up to check the condition only in the first sheet and then it copies all the sheet in the same folder.
View 2 Replies
View Related
Jan 30, 2003
I have a spreadsheet that imports data, manipulates it then deletes 2 of the sheets then saves the file under a different name to the network. Is there any way to save this new worksheet without it storing the macros - so when the user open it, only the data is there and they get no prompt to enable macros?
View 6 Replies
View Related
Aug 16, 2012
I have a worksheet that has a number in cell K5 - the number is generated on "file open" code and is custom formatted as "TN"0000. Thus 1 appears as TN0001, 2 as TN0002 and so on. I am trying to save a copy of the workbook based on the this cells contents i.e. TN0001.xls, TN0002.xls etc. but the files are saved as 1.xls or 2.xls. The code I am using is
ActiveWorkbook.SaveAs Filename:="C:DataExcelFORMSDelivery Note" & Range("K5") & ".xls", _
FileFormat:=xlNormal, ReadOnlyRecommended:=True, CreateBackup:=False
I know I must make reference to format within the above....but how? if try something like
" & Format(Range("K5").Value, ("TN""0000")) & ".xls"
I get TN00000.xls
View 2 Replies
View Related
Jul 7, 2008
I am setting up a "save as" macro that saves a file by replacing another file in a folder. Even though the macro has been recorded by approving the replacement (the prompt appears "the file --- already exists. Do you want to replace the existing file?"), when I run the macro, I am again prompted about replacing the file. Is it possible to avoid the prompt so the file is automatically saved by replacing the named file?
View 9 Replies
View Related
Aug 30, 2013
I 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.
View 1 Replies
View Related
Mar 16, 2007
found the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?
Sub SaveAsCell()
Dim strName As String
On Error Goto InvalidName
strName = Sheet1. Range("V77")
ActiveWorkbook.SaveAs strName
It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?
Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.
View 9 Replies
View Related
May 6, 2009
Explanation of problem:
I have 2 files: data.xls and calculation.xls
In data.xls, I enter a value in cell A1
In calculation.xls, I go to cell B1 and enter a reference to cell A1 on file data.xls. The following formula is entered in cell B1 of calculation.xls: =[data.xls]Sheet1!$A$1
Ok so far so good. Now when I change the value in data.xls, the value is updated automatically in the calculation.xls cell. And this is done even if I first update the value in data.xls and then later open the calculation.xls file (it will ask me to update).
Now let's say I have calculation.xls closed and I am updating the data.xls file. I enter a new entry above cell A1 in data.xls. So now the contents of cell A1 are shifed down to cell A2.
When I re-open my calculation.xls file, it is still referencing cell A1 in data.xls. Is there any way for me to link it in a way so that calculation.xls would know that it now needs to reference cell A2 since that cell got shifted down?
If both files are open then it works fine. But the way this setup will work, I will not always have them both open.
Also, if I copy the contents of data.xls onto another sheet on calculation.xls and reference the cell in the same file, then of course it works. But I would like it to work the other way with the files separate.
View 9 Replies
View Related
Jan 12, 2012
I have multiple files in one folder: first.xlsx, second.xlsx, third.xlsx etc
In each of them the first cell is calle fist_indicator, second_indicator etc
In a new document i have a column A with all the names: fist, second, third
In a hidden second column i have ="="&"["&ZS(-1)&".xlsx]Tabelle1!"&"Z1S1" which gives the value of =[first.xlsx]Tabelle1!Z1S1 , =[second.xlsx]Tabelle1!Z1S1
So now when i try in column c to just say = R1C2 it gives a #NAME? error
Summarized, i have a manually filled in column A and with it i want to make another column that shows the first cell of each document (so it uses the fist colum + the other directions to show the first cell)
if i just add =[first.xlsx]Tabelle1!Z1S1 it works, but i want to be able to make this first.xlsx be changed according to the entry in column A.
View 1 Replies
View Related
Nov 12, 2008
I want to use a macro to open an existing file on my c drive where the reference to that file is contained in a cell.
E.g. In my sheet, the cell A1 contains the following text "C:Documents and SettingsTest". Therefore the file I want to open using a macro will be Test.xls.
However the file I wish to open (and therefore the reference text in cell A1) could change (and it could also change to be a word document) so it needs to be able to adapt when a new valid file (and document type) reference is entered into the cell.
Also, as an aside, when I want to change the file referred to in A1, the easiest way I've found is to find the file I want in windows explorer, go to properties and copy the file reference and then paste it into A1. However the reference (from the properties tab) seems to miss the ".xls" or ".doc" at the end (see above). Is this an issue? If yes, then I'm happy to manually type the .xls or .doc extension at the end.
Is there an easier way to do this?
View 10 Replies
View Related
Jul 29, 2009
I have the following (working) formula that pulls the largest value from an array in a closed workbook on a network share:
=LARGE('\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL SHAREDM806 ReportsFY 2008-2009Clarion countyJanuary 09[385-4009.xls]M-805'!$F$11:$G$41,1)
I want to be able to use a cell value within the open workbook to change the name of the referenced workbook (i.e. the 385-4009 portion of the formula) in the fromula
I have atempted to use the following:
=LARGE('\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL SHAREDM806 ReportsFY 2008-2009Clarion countyJanuary 09["'"p8"'.xls]M-805'!$F$11:$G$41,1)
but excel returns an error and highlights the following portion of the formula:
'\pdfpcoclu2k302pd10fap2k01dist10AdminFISCAL
View 2 Replies
View Related
Jan 9, 2009
I'm using a spreadsheet to assimilate data from a number of different files. Now I need to vlookup on this compiled data, but the vlookup cannot find the value because it is not looking at the value of the data in the cell, but rather the formula. How do I get vlookup to search a column by the value displayed in that cell, rather than the reference to some other file?
View 9 Replies
View Related
Jan 16, 2008
Trying to reference cells in a closed workbook for the first time, and am having problems. The formula in my cell is: ='H:Trial BalanceTESTING[12-07-2007.csv]12-07-2007'!$P2
At first it wouldn't work, and I always had a #REF! in that cell, but when I opened the csv file the correct value showed in the cell that has the formula. Is it not possible to have it work without me having to open the csv file as well?
View 2 Replies
View Related
Feb 18, 2009
I am trying to save my workbook in a specific directory with a specific filename and to incorporate a date field from within the spreadsheet. I have changed the format of the cell so it does not include / as i know this would not work. Saving it is the easy part but adding in the field from the spreadsheet is where im falling over.
View 2 Replies
View Related
Mar 6, 2014
I want to save my file as a pdf based on cell reference A4.
This is what i have
[Code] ....
View 2 Replies
View Related
May 21, 2007
i have a order form that is customer dependant. the cust name on the form is manually entered by the user within a msg box. i have specific cusotmer folders on a shared machines C drive where i would like to save these excel docs to.
i understand that the cust name must match the name of the file and there needs to be a way to save those that do not have specific folder matching what was entered as the customer name.
the overall goal is to have the file name saved as CustomerNameDeliveryDateInvoiceNumber.xls where CustName is from cell "M3", delivery date is "Q7" and InvoiceNumber is "Q1".
Sub OrderFormSave()
Dim strCustFileName As String
savefile = "\SrSharedDocsCSPSharedFILESCustomerOrderForms & strCustFileName & "
ActiveWorkbook.SaveCopyAs savefile
End Sub
View 9 Replies
View Related
Apr 9, 2008
I need to reference a cell to another cell in a different workbook (not opened), however the name of this workbook contains a variable in its name. Something like:
='C:[Week" & variable &".xls]R23'!$D$3
where variable is the number of the week. I have tried using a cell name instead of a variable: ='C:[Week=($B$3).xls]R23'!$D$3
and I get a dialog box to update values by selecting a file.
View 4 Replies
View Related
Jun 19, 2014
I'm trying to make a vba that would save the file with the end date that will reference to a certain cell that user selects it. Working Sheet is a name of a File, and Current Sheet is a name of the panel where user will select the date.
ActiveWorkbook.SaveAs Filename:= WorkingSheet("Control Sheet").Range("D3").Value & Format(Date, "yyyymm") & ".xlsm", FileFormat:=51
But here i seem to missing the location
And I tried this one:
ActiveWorkbook.SaveAs Filename:="X:Work2014WorkingSheet& Format(Date,"yyyymm"). & ".xlsm" _, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
but it keeps popping up as 'expected end of statement' for yyymm?
View 8 Replies
View Related
Feb 8, 2008
I'm trying to get Mac OS X Excel VBA to understand this code however the ChDir reference and the filepath seem to be causing problems. I would like to specify in the code where the file should be stored but I did try removing the filepath section altogether but to no avail.
Option Explicit
Sub SvMe()
'Saves filename as value of A1 plus the current date
Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = Range("A1").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
' Change directory to suit your PC, including USER NAME
ChDir _
"C:Documents and Settings USER NAME Desktop"
ActiveWorkbook. SaveAs Filename:=newFile
End Sub
View 2 Replies
View Related
May 8, 2007
I have a spreadsheet file (excel 2003). I'd like convert it in CSV with delim.
But when I save it appear message that say "the file contains some things not compatible with CSV with delimiter" then I can save it and lose information. After when I open it with Word it looks ok but delimiter is ";"
I set delimiter in windows and inside excel too to ","
But I can't undertand what are things not compatible. there are only words and number as general format....(there aren't "," and each other strange character).
View 12 Replies
View Related
Mar 14, 2013
How can I modify this code to simply display the pdf and not have it save to anywhere.....
Sub SavePDF_Click()
Dim UserSite As String
UserSite = Sheets("Site").Range("D102").Value
Sheets("View your Graphs").Select
Sheets("View your Graphs").Activate
[Code]...
View 1 Replies
View Related
Dec 8, 2008
How do I save the spreadsheet with the current name of the spreadsheet in a macro?
Example:
I want to save the spreadsheet to a network drive using the current name of the spreadsheet (it changes every other day). But I dont know to put the name in the SAVE statement. Here is what I have now:
View 4 Replies
View Related
Apr 20, 2009
Saving WORKSHEET file. I'm using this code:
View 4 Replies
View Related
Aug 24, 2009
I am trying to create a .csv file from an Excel file using VBA and when I use the ActiveWorkbook.SaveAs command, only the first row of text contains " quotes around the text. I have multiple rows that must contain the quotes. I have tried using """", and Chr(34), but then that puts """ (3 quotes) around my text. Is there a different function that I should be attempting to use? I am using concatanation to create a field where I would like the quoted text and am using the ActiveCell.Value command (ActiveCell.Text didn't seem to make any difference).
View 9 Replies
View Related
Oct 22, 2009
I have the below code to save a file once cretaed from master data, but it keeps failing on me.
View 3 Replies
View Related
Nov 26, 2013
I have a code that performs a task for me and will save the file at the end. It currently saves as a name I have selected and today's date. The problem I now face is that I will be completing this task several times a day and it will not work as the file names will be the same. Is there any way to save a unique name each time?
My code to save below:
Code:
ActiveWorkbook.SaveAs Filename:= _ "T:Lender UpdatesLender UpdatesLloydsCompleteAcknowledged " & Format(Date, "dd.mm.yyyy") & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
View 3 Replies
View Related
May 2, 2007
I am trying to develop a routine that will save the file I am currently working on, then save the current tab as a *.csv file with the name equal to cell "C1" in the same directory as the original file. Then I would like it to close the .csv file and reopen the original file all while being generic with the naming so that if the file name and location changes, the script will still work. I recorded a macro to get me started.
View 9 Replies
View Related
