| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Only Values
Below is a code that works great and what it does is that it exports a
worksheet from the current workbook to a new workbook. The problem I'm having is that I only want to copy the values and leave the formulas and references behind. Any help would be appreciated. Thank you for your help! Dim message As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6") FileName1 = Range("M6") If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & ", does not exist." & Chr(10) _ & "Enter a new directory path (cell F6).", 0, "Not today my friend.") Else If MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & ", already exists." & Chr(10) _ & "A copy of this file has NOT been saved.", 0, "Not today my friend.") Else 'FolderName1 = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls), *.xls") Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal 'ActiveWorkbook.SaveCopyAs FileName1 'Range("H1") = "I've Been Exported" ActiveWindow.Close End If End If |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Only Values
ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal
After the above line, add this: With Workbooks(FileName1).Sheets(1).Cells .Value = Value End With "NFL" wrote in message ... Below is a code that works great and what it does is that it exports a worksheet from the current workbook to a new workbook. The problem I'm having is that I only want to copy the values and leave the formulas and references behind. Any help would be appreciated. Thank you for your help! Dim message As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6") FileName1 = Range("M6") If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & ", does not exist." & Chr(10) _ & "Enter a new directory path (cell F6).", 0, "Not today my friend.") Else If MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & ", already exists." & Chr(10) _ & "A copy of this file has NOT been saved.", 0, "Not today my friend.") Else 'FolderName1 = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls), *.xls") Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal 'ActiveWorkbook.SaveCopyAs FileName1 'Range("H1") = "I've Been Exported" ActiveWindow.Close End If End If |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Only Values
After you have created the new copy workbook, copy all the cells and paste
then as values, then save again. Workbooks([FileName1]).worksheets("Export").cells.copy Workbooks([FileName1]).worksheets("Export").cells.PasteSpecial Paste:=xlPasteValues This will remove all formulas and leave just the values in each cell. "NFL" wrote in message ... Below is a code that works great and what it does is that it exports a worksheet from the current workbook to a new workbook. The problem I'm having is that I only want to copy the values and leave the formulas and references behind. Any help would be appreciated. Thank you for your help! Dim message As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6") FileName1 = Range("M6") If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & ", does not exist." & Chr(10) _ & "Enter a new directory path (cell F6).", 0, "Not today my friend.") Else If MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & ", already exists." & Chr(10) _ & "A copy of this file has NOT been saved.", 0, "Not today my friend.") Else 'FolderName1 = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls), *.xls") Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal 'ActiveWorkbook.SaveCopyAs FileName1 'Range("H1") = "I've Been Exported" ActiveWindow.Close End If End If |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Only Values
I entered the code as shown below and got a compile error msg "Variable Not
Defined". I pressed the Debug button and it highlighed the word Value right after the equal sign. :-( "JLGWhiz" wrote: ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal After the above line, add this: With Workbooks(FileName1).Sheets(1).Cells .Value = Value End With "NFL" wrote in message ... Below is a code that works great and what it does is that it exports a worksheet from the current workbook to a new workbook. The problem I'm having is that I only want to copy the values and leave the formulas and references behind. Any help would be appreciated. Thank you for your help! Dim message As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6") FileName1 = Range("M6") If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & ", does not exist." & Chr(10) _ & "Enter a new directory path (cell F6).", 0, "Not today my friend.") Else If MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & ", already exists." & Chr(10) _ & "A copy of this file has NOT been saved.", 0, "Not today my friend.") Else 'FolderName1 = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls), *.xls") Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal 'ActiveWorkbook.SaveCopyAs FileName1 'Range("H1") = "I've Been Exported" ActiveWindow.Close End If End If . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Only Values
My typo. The second value should also have a period
With Workbooks(FileName1).Sheets(1).Cells .Value = .Value "NFL" wrote in message ... I entered the code as shown below and got a compile error msg "Variable Not Defined". I pressed the Debug button and it highlighed the word Value right after the equal sign. :-( "JLGWhiz" wrote: ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal After the above line, add this: With Workbooks(FileName1).Sheets(1).Cells .Value = Value End With "NFL" wrote in message ... Below is a code that works great and what it does is that it exports a worksheet from the current workbook to a new workbook. The problem I'm having is that I only want to copy the values and leave the formulas and references behind. Any help would be appreciated. Thank you for your help! Dim message As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6") FileName1 = Range("M6") If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & ", does not exist." & Chr(10) _ & "Enter a new directory path (cell F6).", 0, "Not today my friend.") Else If MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & ", already exists." & Chr(10) _ & "A copy of this file has NOT been saved.", 0, "Not today my friend.") Else 'FolderName1 = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls), *.xls") Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal 'ActiveWorkbook.SaveCopyAs FileName1 'Range("H1") = "I've Been Exported" ActiveWindow.Close End If End If . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Only Values
I tried your code and I got a runtime error 9: subscript out of range
message. I pressed the debug and it pointed to the first line of your code. :-( "Paul W Smith" wrote: After you have created the new copy workbook, copy all the cells and paste then as values, then save again. Workbooks([FileName1]).worksheets("Export").cells.copy Workbooks([FileName1]).worksheets("Export").cells.PasteSpecial Paste:=xlPasteValues This will remove all formulas and leave just the values in each cell. "NFL" wrote in message ... Below is a code that works great and what it does is that it exports a worksheet from the current workbook to a new workbook. The problem I'm having is that I only want to copy the values and leave the formulas and references behind. Any help would be appreciated. Thank you for your help! Dim message As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6") FileName1 = Range("M6") If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & ", does not exist." & Chr(10) _ & "Enter a new directory path (cell F6).", 0, "Not today my friend.") Else If MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & ", already exists." & Chr(10) _ & "A copy of this file has NOT been saved.", 0, "Not today my friend.") Else 'FolderName1 = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls), *.xls") Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal 'ActiveWorkbook.SaveCopyAs FileName1 'Range("H1") = "I've Been Exported" ActiveWindow.Close End If End If . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Only Values
Well bummer .... now I got a different runtime error 9 message saying
subscript out of range. "JLGWhiz" wrote: My typo. The second value should also have a period With Workbooks(FileName1).Sheets(1).Cells .Value = .Value "NFL" wrote in message ... I entered the code as shown below and got a compile error msg "Variable Not Defined". I pressed the Debug button and it highlighed the word Value right after the equal sign. :-( "JLGWhiz" wrote: ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal After the above line, add this: With Workbooks(FileName1).Sheets(1).Cells .Value = Value End With "NFL" wrote in message ... Below is a code that works great and what it does is that it exports a worksheet from the current workbook to a new workbook. The problem I'm having is that I only want to copy the values and leave the formulas and references behind. Any help would be appreciated. Thank you for your help! Dim message As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6") FileName1 = Range("M6") If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & ", does not exist." & Chr(10) _ & "Enter a new directory path (cell F6).", 0, "Not today my friend.") Else If MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & ", already exists." & Chr(10) _ & "A copy of this file has NOT been saved.", 0, "Not today my friend.") Else 'FolderName1 = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls), *.xls") Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal 'ActiveWorkbook.SaveCopyAs FileName1 'Range("H1") = "I've Been Exported" ActiveWindow.Close End If End If . . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Only Values
Sorry for the delayed response.
From what I can determine from your code, the value in M6 is a file name. The way it is used elsewhere in the code, FileName1 appears to be a variable that holds that file name. If all that is true the Workbooks(FileName1) should return the file object that you want to convert to values only in the cells that display data. Based on those assumptions, the code worked when I tested it. I do not know why you continue to get errors unless the assumptions are incorrect, in which case you should get an error before you reach that line. "NFL" wrote in message ... Well bummer .... now I got a different runtime error 9 message saying subscript out of range. "JLGWhiz" wrote: My typo. The second value should also have a period With Workbooks(FileName1).Sheets(1).Cells .Value = .Value "NFL" wrote in message ... I entered the code as shown below and got a compile error msg "Variable Not Defined". I pressed the Debug button and it highlighed the word Value right after the equal sign. :-( "JLGWhiz" wrote: ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal After the above line, add this: With Workbooks(FileName1).Sheets(1).Cells .Value = Value End With "NFL" wrote in message ... Below is a code that works great and what it does is that it exports a worksheet from the current workbook to a new workbook. The problem I'm having is that I only want to copy the values and leave the formulas and references behind. Any help would be appreciated. Thank you for your help! Dim message As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6") FileName1 = Range("M6") If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " & FolderName1 & ", does not exist." & Chr(10) _ & "Enter a new directory path (cell F6).", 0, "Not today my friend.") Else If MyComputer.FileExists(FileName1) = True Then message = MsgBox("The file name: " & FileName1 & ", already exists." & Chr(10) _ & "A copy of this file has NOT been saved.", 0, "Not today my friend.") Else 'FolderName1 = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls), *.xls") Sheet18.Visible = xlSheetVisible Sheets("Export").Copy ActiveWorkbook.SaveAs Filename:=FileName1, FileFormat:=xlNormal 'ActiveWorkbook.SaveCopyAs FileName1 'Range("H1") = "I've Been Exported" ActiveWindow.Close End If End If . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Export to CSV values | Excel Programming | |||
| Export Conditional Formatting Values | Excel Worksheet Functions | |||
| Save as or export with values only | Excel Worksheet Functions | |||
| Export values in a text file | Excel Programming | |||
Linear Mode
