+ Reply to Thread
Results 1 to 9 of 9

Exporting Workbook without Macro/Formulas

Hybrid View

JPSIMMON Exporting Workbook without... 10-27-2015, 12:52 PM
shg Re: Exporting Workbook... 10-27-2015, 12:54 PM
JPSIMMON Re: Exporting Workbook... 10-27-2015, 12:57 PM
shg Re: Exporting Workbook... 10-27-2015, 01:01 PM
JPSIMMON Re: Exporting Workbook... 10-27-2015, 01:04 PM
JPSIMMON Re: Exporting Workbook... 10-27-2015, 01:06 PM
shg Re: Exporting Workbook... 10-27-2015, 02:10 PM
JPSIMMON Re: Exporting Workbook... 10-27-2015, 03:47 PM
shg Re: Exporting Workbook... 10-27-2015, 03:50 PM
  1. #1
    Forum Contributor
    Join Date
    08-23-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 365
    Posts
    166

    Exporting Workbook without Macro/Formulas

    Looking to make a copy of open workbook and save as it to a specific folder without any of the formulas/macros from the original.
    I keep have trouble on
    Output.SaveAs FileName, XlFileFormat.xlOpenXMLWorkbook
    This is the full code:
    Sub Flatten()
    
        Dim Output As Workbook
        Dim Current As String
        Dim FileName As String
    
        Set Output = ThisWorkbook
        Current = ThisWorkbook.FullName
    
        Application.DisplayAlerts = False
    
        Dim SH As Worksheet
        For Each SH In Output.Worksheets
    
            SH.UsedRange.Copy
            SH.UsedRange.PasteSpecial xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=True, Transpose:=False
    
        Next
    
        FileName = ThisWorkbook.Path & "S:\Location\" & "4XX.xlsx"
        Output.SaveAs FileName, XlFileFormat.xlOpenXMLWorkbook
        Workbooks.Open Current
        Output.Close
        Application.DisplayAlerts = True
    End Sub

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Exporting Workbook without Macro/Formulas

    What's the value of Filename? Does it look like a valid path?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Contributor
    Join Date
    08-23-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 365
    Posts
    166

    Re: Exporting Workbook without Macro/Formulas

    FileName = ThisWorkbook.Path & "S:\Location\" & "4XX.xlsx"
    This is the destination to which I want to save the "saved as" flattened file in.

    the 4xx is just for the thread, the workbook name will always be a 3-digit number like 234.xlsx
    Last edited by JPSIMMON; 10-27-2015 at 01:02 PM.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Exporting Workbook without Macro/Formulas

    Quote Originally Posted by JPSIMMON View Post
    The file name is a 3-digit number like "123.xlsx" I just put "4XX" for the post
    Not my question.

    After this statement executes:

    FileName = ThisWorkbook.Path & "S:\Location\" & "4XX.xlsx"
    ... what is the value of the variable Filename?

  5. #5
    Forum Contributor
    Join Date
    08-23-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 365
    Posts
    166

    Re: Exporting Workbook without Macro/Formulas

    Sorry shg,
    "4XX Original.xlsx" is the name of the original workbook, is that what you mean?

  6. #6
    Forum Contributor
    Join Date
    08-23-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 365
    Posts
    166

    Re: Exporting Workbook without Macro/Formulas

    SHG, you were right I have an invalid path for FileName.

    Essentially I just need to know where to put the destined location to save my new flattened file

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Exporting Workbook without Macro/Formulas

    So where do you want to save it?

  8. #8
    Forum Contributor
    Join Date
    08-23-2013
    Location
    Alberta, Canada
    MS-Off Ver
    Excel 365
    Posts
    166

    Re: Exporting Workbook without Macro/Formulas

    This is the destination folder:
    "S:\Location\"

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Exporting Workbook without Macro/Formulas

      Output.SaveAs Filename:="S:\Location\4XX.xlsx", _
                    FileFormat:=xlOpenXMLWorkbook

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Exporting Worksheets as xlsm with Values not Formulas
    By JPSIMMON in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-21-2015, 05:49 PM
  2. Exporting each sheet to a new workbook and saving workbook in new folder with timestamp
    By amphinomos in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-19-2013, 11:19 AM
  3. [SOLVED] Exporting columns into new workbook as text values not formulas
    By ryanexceln00b in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-31-2013, 08:37 AM
  4. Importing and Exporting data from workbook to workbook in excel
    By dans123 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-09-2012, 03:40 AM
  5. Exporting Workbook with new name
    By joehjas in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-13-2011, 03:08 AM
  6. Replies: 0
    Last Post: 08-19-2010, 03:44 AM
  7. Exporting formulas from UK to Germany
    By AndySuk in forum Excel General
    Replies: 3
    Last Post: 02-19-2009, 06:35 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1