If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Macro to automatically save document with cell reference as filena
I have tried to create a workbook that automatically grabs the filename and
saves the document with some problems Both methods appear to work, but Excel always crashes. There should be a way to accomplish this without a crash. Method 1: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim objFolders As Object Set objFolders = CreateObject("WScript.Shell").SpecialFolders ActiveWorkbook.SaveAs objFolders("mydocuments") & "\Workflow Moves\" & Sheets("Sheet1").Range("F1").Value & ".xls" End Sub Method 2: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) MyDocsPath = Environ$("USERPROFILE") & "\My Documents\Workflow Moves\" ActiveWorkbook.SaveAs MyDocsPath & Sheets("Sheet1").Range("F1").Value & ".xls" End Sub The bottom line is that when a user saves the file, the file should end up in a folder called "Workflow Moves" which is in each user's "My Documents" location and the name should be the value of cel F1 plus the XLS extension. I'm using Office 2003. I would like for this to work in 2007 as well. Any help? Thanks in advance Erich |
#2
|
|||
|
|||
Macro to automatically save document with cell reference as filena
Erich,
You need to disable events, save the workbook under the new name, and cancel the first user-initiated save: Excel crashes because it gets into an infinite loop of BeforeSave events. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim objFolders As Object Set objFolders = CreateObject("WScript.Shell").SpecialFolders Application.EnableEvents = False ActiveWorkbook.SaveAs objFolders("mydocuments") & "\Workflow Moves\" & Sheets("Sheet1").Range("F1").Value & ".xls" Application.EnableEvents = True Cancel = True End Sub HTH, Bernie MS Excel MVP "Erich" wrote in message ... I have tried to create a workbook that automatically grabs the filename and saves the document with some problems Both methods appear to work, but Excel always crashes. There should be a way to accomplish this without a crash. Method 1: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim objFolders As Object Set objFolders = CreateObject("WScript.Shell").SpecialFolders ActiveWorkbook.SaveAs objFolders("mydocuments") & "\Workflow Moves\" & Sheets("Sheet1").Range("F1").Value & ".xls" End Sub Method 2: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) MyDocsPath = Environ$("USERPROFILE") & "\My Documents\Workflow Moves\" ActiveWorkbook.SaveAs MyDocsPath & Sheets("Sheet1").Range("F1").Value & ".xls" End Sub The bottom line is that when a user saves the file, the file should end up in a folder called "Workflow Moves" which is in each user's "My Documents" location and the name should be the value of cel F1 plus the XLS extension. I'm using Office 2003. I would like for this to work in 2007 as well. Any help? Thanks in advance Erich |
#3
|
|||
|
|||
Macro to automatically save document with cell reference as fi
Bernie,
That did just the trick. Thank you so much for your help. Your explanation helps me understand why the same code works fine as a Macro that's manually triggered as opposed to one that is run after the BeforeSave event. Erich "Bernie Deitrick" wrote: Erich, You need to disable events, save the workbook under the new name, and cancel the first user-initiated save: Excel crashes because it gets into an infinite loop of BeforeSave events. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim objFolders As Object Set objFolders = CreateObject("WScript.Shell").SpecialFolders Application.EnableEvents = False ActiveWorkbook.SaveAs objFolders("mydocuments") & "\Workflow Moves\" & Sheets("Sheet1").Range("F1").Value & ".xls" Application.EnableEvents = True Cancel = True End Sub HTH, Bernie MS Excel MVP "Erich" wrote in message ... I have tried to create a workbook that automatically grabs the filename and saves the document with some problems Both methods appear to work, but Excel always crashes. There should be a way to accomplish this without a crash. Method 1: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim objFolders As Object Set objFolders = CreateObject("WScript.Shell").SpecialFolders ActiveWorkbook.SaveAs objFolders("mydocuments") & "\Workflow Moves\" & Sheets("Sheet1").Range("F1").Value & ".xls" End Sub Method 2: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) MyDocsPath = Environ$("USERPROFILE") & "\My Documents\Workflow Moves\" ActiveWorkbook.SaveAs MyDocsPath & Sheets("Sheet1").Range("F1").Value & ".xls" End Sub The bottom line is that when a user saves the file, the file should end up in a folder called "Workflow Moves" which is in each user's "My Documents" location and the name should be the value of cel F1 plus the XLS extension. I'm using Office 2003. I would like for this to work in 2007 as well. Any help? Thanks in advance Erich . |
Thread Tools | |
Display Modes | |
|
|