Results 1 to 10 of 10

Saving as a macro enabled workbook using VBA code

This is a discussion on Saving as a macro enabled workbook using VBA code within the Excel Questions forums, part of the Question Forums category; right ho, may be incredibly straightforward this (I hope it is) I'm using excel 2007 - i've created a macro ...

  1. #1
    New Member
    Join Date
    Sep 2011
    Posts
    5

    Default Saving as a macro enabled workbook using VBA code

    right ho,

    may be incredibly straightforward this (I hope it is) I'm using excel 2007 - i've created a macro enabled template file, and I'm wanting to include some code that will allow me to automatically save in a folder location using the name from a cell, but I still want to be able to use macros in the saved spreadsheet, i assumed it would be a simple case of changing the file extension to .xlsm, but this returns an error, i can get the file to save as a standard workbook, so i'm a bit lost - below is the code i've been using

    Sub SaveBook()
    Dim sFile As String
    sFile = Range("J8") & ".xlsx"
    ActiveWorkbook.SaveAs Filename:="O:\Mobile Plant\General\Budgeting & part expenditure\" & sFile
    End Sub


    any help most appreciated!

    Cheers

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,449

    Default Re: Saving as a macro enabled workbook using VBA code

    Try

    Code:
    Sub SaveBook()
    Dim sFile As String
    sFile = Range("J8").Value & ".xlsm"
    ActiveWorkbook.SaveAs Filename:="O:\Mobile Plant\General\Budgeting & part expenditure\" & sFile, FileFormat:=52
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,330

    Default Re: Saving as a macro enabled workbook using VBA code

    Welcome to MrExcel.

    Try:

    Code:
    ActiveWorkbook.SaveAs Filename:="O:\Mobile Plant\General\Budgeting & part expenditure\" & sFile, FileFormat:=52
    In Excel 2007-2010, SaveAs requires both the FileFormat parameter and the correct file extension.
    Microsoft MVP - Excel

  4. #4
    New Member
    Join Date
    Sep 2011
    Posts
    5

    Default Re: Saving as a macro enabled workbook using VBA code

    Both of the FileFormat:=52 return a Run-time error '1004'

    method 'saveAs' of object '_Workbook' failed

    at a loss??

  5. #5
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,449

    Default Re: Saving as a macro enabled workbook using VBA code

    What is in J8?
    HTH, Peter
    Please test any code on a copy of your workbook.

  6. #6
    New Member
    Join Date
    Sep 2011
    Posts
    5

    Default Re: Saving as a macro enabled workbook using VBA code

    J8 is the name that I want the spreadsheet to save as, it's Week commencing and then the date 13.10.11

  7. #7
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,449

    Default Re: Saving as a macro enabled workbook using VBA code

    Then maybe

    Code:
    Sub SaveBook()
    Dim sFile As String
    sFile = "Week commencing " & Format(Date, "dd-mm-yy") & ".xlsm"
    ActiveWorkbook.SaveAs Filename:="O:\Mobile Plant\General\Budgeting & part expenditure\" & sFile, FileFormat:=52
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  8. #8
    New Member
    Join Date
    Sep 2011
    Posts
    5

    Default Re: Saving as a macro enabled workbook using VBA code

    that does work thanks, but i need it to refer to the cell reference as part of the save name,

    sorry to be a pain....

  9. #9
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,330

    Default Re: Saving as a macro enabled workbook using VBA code

    Replace the dots in the date in J8 with dashes.
    Microsoft MVP - Excel

  10. #10
    New Member
    Join Date
    Sep 2011
    Posts
    5

    Default Re: Saving as a macro enabled workbook using VBA code

    that's sorted it thanks, although i'm also using the other coding VoG now too on a different sheet!

    Cheers guys

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com