+ Reply to Thread
Results 1 to 6 of 6

Export sheet to be saved as a .xls workbook

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2024
    Posts
    2,873

    Export sheet to be saved as a .xls workbook

    I have written code to export a sheet called "Imported Data" as a .xls workbook



     Sub Export_SheetsasXls()
        Dim NewName As String
            Dim ws As Worksheet
         
            
        With Application
            .ScreenUpdating = False
             
                   
            On Error GoTo ErrCatcher
            Sheets(Array("Imported Data")).Copy
            On Error GoTo 0
             
            
            For Each ws In ActiveWorkbook.Worksheets
                ws.Cells.Copy
                ws.[A1].PasteSpecial Paste:=xlValues
                ws.Cells.Hyperlinks.Delete
                Application.CutCopyMode = False
                Cells(1, 1).Select
                ws.Activate
            Next ws
            Cells(1, 1).Select
             
          
             
             
            ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & "BR1 Sales Upload" & ".xls"
            ActiveWorkbook.Close SaveChanges:=False
             
            .ScreenUpdating = True
        End With
        Exit Sub
         
    ErrCatcher:
        MsgBox "Specified sheets do not exist within this workbook"
    End Sub

    when Opening the file I get a message "file format and extension don't match-see attached message


    It wold be appreciated if someone could amend my code
    Attached Images Attached Images

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    MS365 V.2406
    Posts
    2,310

    Re: Export sheet to be saved as a .xls workbook

    Could you use SaveAs...
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "BR1 Sales Upload" & ".xls", FileFormat:=xlExcel9795

  3. #3
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2024
    Posts
    2,873

    Re: Export sheet to be saved as a .xls workbook

    Thanks for you input


    I have amended the code and get a run time error and code below is highlighted

    Method 'Saveas' of object method_Workbook failed


       ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "BR1 Sales Upload" & ".xls", FileFormat:=xlExcel9795

    Kindly test and amend

  4. #4
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    MS365 V.2406
    Posts
    2,310

    Re: Export sheet to be saved as a .xls workbook

    Try this barebones modification. I had problems but got this to work...
    Sub Export_SheetsasXls()
        Application.ScreenUpdating = False
        
        Sheets(Array("Imported Data")).Copy
        Worksheets(1).UsedRange = Worksheets(1).UsedRange.Value2
        Worksheets(1).Cells.Hyperlinks.Delete
        
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "BR1 Sales Upload" & ".xls", 56
        ActiveWorkbook.Close SaveChanges:=False
        
        Application.ScreenUpdating = True
    End Sub

  5. #5
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2024
    Posts
    2,873

    Re: Export sheet to be saved as a .xls workbook

    Thanks for the help. Code works perfectly

  6. #6
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    MS365 V.2406
    Posts
    2,310

    Re: Export sheet to be saved as a .xls workbook

    You're welcome. Glad I could help.

+ 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. Workbook.saved = true status changes on activation of another sheet
    By MrtKr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-15-2014, 06:32 PM
  2. [SOLVED] Specify export pdf file location is location workbook is saved.
    By dantray02 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-30-2014, 01:13 PM
  3. locking cells in a sheet when a workbook is saved
    By fantasticsemi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-20-2013, 08:35 AM
  4. How to export data from one sheet in a workbook to another sheet in another workbook
    By Billybong in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-26-2012, 09:50 AM
  5. Export Sheet to new Workbook
    By adgjqetuo in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-25-2010, 06:55 PM
  6. hyperlink to sheet in workbook saved as html
    By Y Sbuty in forum Excel General
    Replies: 0
    Last Post: 03-26-2006, 07:00 PM
  7. [SOLVED] Hyperlink to specific sheet; workbook saved as html
    By Bawn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-07-2005, 09:34 AM

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