+ Reply to Thread
Results 1 to 4 of 4

VBA / Macro to Select File and Save As

Hybrid View

  1. #1
    Registered User
    Join Date
    09-22-2014
    Location
    Los Angeles, CA
    MS-Off Ver
    2011
    Posts
    39

    VBA / Macro to Select File and Save As

    Right now I receive "Spreadsheet A" from a coworker and then I run a macro in "Spreadsheet B" to import the pertinent data from "Spreadsheet A" into "Spreadsheet B". In order for that all to run correctly I have to change the file name of "Spreadsheet A" to match the file name in the macro/vba code in "Spreadsheet B". I am hoping there is a macro/vba I could use to open a dialog box to browse to select "Spreadsheet A", open it, and then save it as a new file name. Currently I have a very basic code that allows me to navigate to and open "Spreadsheet A" and then a dialog box to Save As but after those run "Spreadsheet A" has not been saved to the selected location. I know I am missing a step but can't quite figure it out.

    Here is my current code:

    Sub openIt() 
        Dim strFile As String 
         
        strFile = Application.GetOpenFilename 
        strFile = Application.GetSaveAsFilename
        If strFile <> "False" Then Workbooks.Open strFile 
    End Sub

    Any and all help is greatly appreciated!! Also, to note - I am on a Mac using Excel 2011

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,138

    Re: VBA / Macro to Select File and Save As

    Sub openIt()
        Dim WB As Workbook
        Dim strFile As String
         
        strFile = Application.GetOpenFilename
        If strFile <> "False" Then
            Set WB = Workbooks.Open(strFile)
        
            strFile = Application.GetSaveAsFilename
            
            WB.SaveAs strFile
        End If
    End Sub
    If you change your import code to implement the WB object above, you wouldn't even have to change the filename.
    David
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    09-22-2014
    Location
    Los Angeles, CA
    MS-Off Ver
    2011
    Posts
    39

    Re: VBA / Macro to Select File and Save As

    Thank you!! That worked beautifully!!

  4. #4
    Forum Contributor
    Join Date
    12-29-2014
    Location
    Indonesia
    MS-Off Ver
    Office 2010, 2013
    Posts
    125

    Re: VBA / Macro to Select File and Save As

    allow me to follow this thread all, thank you

+ 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. Replies: 1
    Last Post: 09-05-2013, 02:39 PM
  2. [SOLVED] Macro to prompt user to select date and save file
    By TheLittlePrince in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-04-2013, 08:34 AM
  3. [SOLVED] [Help]macro to open file browser, to select a location, and save the location to a cell
    By zhuleijia in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-06-2013, 09:56 AM
  4. [SOLVED] Save PDF - Select file location
    By ZeDoctor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-18-2013, 11:48 AM
  5. let user select input file, modify, save
    By alanders in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-25-2010, 06:37 PM

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