Jan 29 2021 06:30 AM
I do have a question, that I seem to be hitting a wall on. Is it possible to update an Excel File Name (Rename an Excel Sheet), and it auto update a specific cell in the workbook? Here is the next part of the question. I need this program to be able to follow the file, regardless where it is saved. Meaning, if update the file on my computer, but email it to someone, I want the formula dynamic. I need it to follow the sheet NOT look for coding to “My documents”, or “My computer” to know what the cell name is. And if someone changes the file name on their computer, after I sent it to them, it auto updates the cell to the new file name they name it.
Jan 29 2021 08:42 AM
@Budman36 It sounds like you want the filename in a cell. The function CELL() offers that sort of. CELL("filename") actually gives the full path/reference but with some additional work you can have it trim just the filename out:
=LET(path,CELL("filename"),start,SEARCH("[",path)+1,end,SEARCH("]",path),fn,MID(path,start,end-start),fn)
if you don't have the newest version of Excel with the LET() function you can use:
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)
but maybe the full path would be even better for you?
Jan 30 2021 11:19 AM
@mtarler The MID works, but I am hitting a road block.
Here is my example of a book name:
21MJNXXX. Customer Name. Job Name. REV 1
I need to separate each section into a cell of there own...
21MJNXXX
Customer Name
Job Name
REV 1
The "Text to Columns" function will not work because it shows the formula. Not the actual text. I have tried =LEFT, =LEFT(RIGHT, .... =LEFT(MID... but I can't seem to get this right? Is it possible do the text to columns with code?
Jan 30 2021 01:00 PM
@Budman36 Yes you can use similar techniques to further divide the filename. I assume a period (.) is between each part so do you want the full name in 1 cell and then break it down or only have the parts?
for example if the =MID() formula is in cell A1 then you could paste this in A2 and copy down:
=TRIM(MID(SUBSTITUTE($A$1,".",REPT(" ",LEN($A$1))),LEN($A$1)*(ROWS($A$1:A1)-1)+1,LEN($A$1)))
Jan 30 2021 01:54 PM
As variant
=LET(txt, A1, delim, ".", nDelim, LEN(txt)-LEN(SUBSTITUTE(txt, delim,"")),
ch, UNICHAR(9999),
n, SEQUENCE(1,nDelim+1),
nn, SEQUENCE(1,nDelim+1,2),
startPos,IFERROR(FIND(ch,SUBSTITUTE(txt,delim,ch,n-1))+1,1),
numChars, IFERROR(INDEX(startPos,nn)-1,LEN(txt)+1)-startPos,
TRIM(MID(txt,startPos,numChars))
)
for
Feb 03 2021 01:22 PM
@mtarler This worked very well, on a local drive, personal computers, etc., but it doesn't work when uploaded to sharepoint? Any ideas?
Feb 03 2021 01:36 PM
CELL("filename") is not implemented for Excel Online
Feb 03 2021 02:14 PM
even if I did, I don't know it would work. file references in sharepoint are, let's say different.
You originally said the problem was that you were e-mailing the file to people and they would save it on their computers and often in the process they would change the name. If it is on sharepoint, why would the name change? What is it that you need this name for? For example, on the desktop you can just give an address to point to other files but in sharepoint due to it being a server and security things (and honestly I guess I don't really know the exact reasons) it isn't that easy. But regardless you are asking for the name of itself, not some other file. I still not clear why.
Feb 03 2021 02:35 PM
@mtarler I made an estimating program. Each quote/job gets its own copy of this program in a unique folder. In this folder we put all customer correspondence, vendor quotes, etc.. I make these folders (that contain these workbooks) 50 to 100 at a time. They are all numbered consecutively, so I use a "Renaming Program" to do them in batches. When you open the WB, I made a reference cell that looks for that unique number in the name and it auto populates the rest of the book. 95% of our work is done on a company shared drive, and the program works well. The 5% (one specific sheet) needs to be updated real time by the field guys. For this sheet alone, I use a Sharepoint with a link that I post on our on-line scheduling program. That also works well. In the past I just simply saved a sheet, and named it manually. I was just trying to streamline the process. 95% of it works awesome. Looks as if this 5% may not work.
Feb 03 2021 02:37 PM
You may vote for this idea here CELL function added to online – Welcome to Excel’s Suggestion Box! (uservoice.com)
As for the rest I agree with @mtarler , it all very depends on concrete scenario how do you work with file.
Feb 03 2021 02:43 PM
Option could be to work with synced by OneDrive folders, in this case CELL() returns URL which includes filename and you need to parse it by bit different way. Or use Office Script in Excel Online only version.
In both cases solutions won't be compatible with one for the network shared files.
Feb 03 2021 03:04 PM - edited Feb 03 2021 03:06 PM
@Budman36 @Budman361530 So if I am getting the gist of this it is something like: you have a whole package of files/forms and such you use to quote a job and let's say each year you create 100 of them for the year and each quote is named 2021-0001, 2021-0002, .... and so in each excel file you want relevant cells that refer to the quote to properly use the actual quote number. But you are using some batch file or something else to create 100 copies and rename them to all the "correct" names like "quote 2020-0001".
So can you make that program also change a cell value in the file? If not then maybe you should make a macro in Excel VBA to create those 100 files and update a cell in each file accordingly. That macro wouldn't be hard to make.
Feb 04 2021 06:47 PM
@mtarler Yes you have the idea, but I do about 50 a month. Each folder has multilabel folders, word docs, and an estimating program that has nine sheets. Each sheet populates with information, and I use macro's to do a lot of the functions. One of the nine sheets I need my Forman to have access to, but I don't want them involved with the rest of the sheets. So I basically have that sheet as a stand alone sheet in each folder. I tried to have that one sheet print or save as a stand alone Work Sheet/book, with a macro, but I couldn't get it to work.
Feb 09 2021 12:35 PM
@Budman361530 Without more info on the sheets and file structure I'm in the dark. If you tried before and couldn't get the macro to work, why didn't it work? Why not post that sheet or at least the macro to this forum so someone can help you fix it and make it work. Make sure to give all the information because it is hard for us to guess what you mean or want or intended.
Feb 14 2021 07:42 AM
@mtarler See your point.... I was trying to keep it simple, and I was making things more complicated. Here is the the coding of my macro. First I am trying to just get it to work, and I can't. Next I was trying to get it to preform like another Macro I have, that works for saving different sheets in a PDF in whatever folder they are in, with whatever name the file is.
First Try.... wont save as a Macro enabled WB. In "debug" it show the wb.saveas line as the issue.
Sub sb_Copy_Save_Worksheet_As_Workbook()
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets("Task Sheet.").Copy Before:=wb.Sheets(1)
wb.SaveAs "C:\Users\Budman361530\Documents\New folder\Tasksheet.xlsx"
End Sub
Second Try... wont allow me to save as an .xlsm
Sub sb_Copy_Save_Worksheet_As_Workbook()
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets("Task Sheet.").Copy Before:=wb.Sheets(1)
wb.SaveAs "C:\Users\Budman361530\Documents\New folder\Tasksheet.xlsm"
End Sub
Ulitmaly tyring to work as this code...., but saving as a Macro WB vs. a PDF.
Sub MacroContractQuote()
'
' Macro1 Macro
'
'
'ChDir _
'"Z:\Shared\Budman361530 Shared\Budman361530 Quote and Jobs\01 - Master Quote Folder Templet REV 1.1 DO NOT USE WORKING ON THIS"
'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
'"Z:\Shared\Budman361530 Shared\Budman361530 Quote and Jobs\01 - Master Quote Folder Templet REV 1.1 DO NOT USE WORKING ON THIS\20MJNXXXX REV 1.1 - Copy.pdf" _
', Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
':=False, OpenAfterPublish:=False
Dim strPath As String
Dim strName As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
strPath = ThisWorkbook.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "/Contract Quote "
'Getting the file name dynamically
strName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))
'Creating the new file location and name
strFile = strName & ".pdf"
strPathFile = strPath & strFile
'export to PDF in current folder
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strPathFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& strPathFile
'MsgBox ("New file: " & strPathFile)
End Sub
Feb 15 2021 04:20 AM - edited Feb 15 2021 04:21 AM
@Budman361530 The SaveAs command is trying to save as the same type it is (default is non-macro) so you need to force it to be xlsm (adding the extension on the name is not enough). Add xlOpenXMLWorkbookMacroEnabled to your SaveAs line like this:
wb.SaveAs "C:\Users\Budman361530\Documents\New folder\Tasksheet.xlsm", xlOpenXMLWorkbookMacroEnabled
Feb 15 2021 01:14 PM
@mtarler That did the trick for saving in a Macro. Thank you. What am I doing wrong with trying to get the code to save dynamically, both Name and Location?
I have a code that I made that saves pages in a PDF, to whatever folder the WB is in at that moment, and pulls the name from the name of the workbook it self. example.... if the WB was located in folder X, and was named 20MJNXXX. Customer 1. Job 1. REV. 1, The PDF would be called Contract Quote. 20MJNXXX. Customer 1. Job 1. REV. 1, and save automatically in folder X.
Here is the code...
Sub MacroContractQuote()
'
' Macro1 Macro
'
'
'ChDir _
'"Z:\Shared\Budman Shared\Budman Quote and Jobs\01 - Master Quote Folder Templet REV. 1"
'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
'"Z:\Shared\Budman Shared\Budman Quote and Jobs\01 - Master Quote Folder Templet REV. 1" _
', Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
':=False, OpenAfterPublish:=False
Dim strPath As String
Dim strName As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
strPath = ThisWorkbook.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "/Contract Quote "
'Getting the file name dynamically
strName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))
'Creating the new file location and name
strFile = strName & ".pdf"
strPathFile = strPath & strFile
'export to PDF in current folder
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strPathFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& strPathFile
'MsgBox ("New file: " & strPathFile)
End Sub
I am trying to get the same type of results from the excel saving macro, but it's not working...
Sub sb_Copy_Save_Worksheet_As_Workbook()
Dim wb As Workbook
Set wb = Workbooks.Add
'Getting the file name dynamically
strName = (Left(ActiveWorkbook.Name,(InStrRev(ActiveWorkbook.Name, ".", -1,"JOB PO List") - 1))
ThisWorkbook.Sheets.Copy Before:=wb.Sheets(1)
wb.SaveAs strPath 'current folder', xlOpenXMLWorkbookMacroEnabled
End Sub