+ Reply to Thread
Results 1 to 4 of 4

Excell 2007 VBA to change to a different drive and save file name made up of cells

  1. #1
    Registered User
    Join Date
    09-21-2012
    Location
    Santa Ana, CA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Excell 2007 VBA to change to a different drive and save file name made up of cells

    What I want to do is have the user input data into the spreadsheet. Then press a SAVE button on the sheet and have it do the following:
    !. Check to make sure data exits in three of the cells and give error messgae if not.
    2. Change to a different drive letter.
    3. Once on that drive chnage to the correct directory using cells from the sheet.
    4. Automatically create the file name to save it as based upon cells in the sheet.

    I have tried the following with mixed (not everything works)results:

    Please Login or Register  to view this content.
    Last edited by Leith Ross; 09-26-2012 at 09:45 PM. Reason: Added Code Tags

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Excell 2007 VBA to change to a different drive and save file name made up of cells

    Hello ml330,

    To force the macro to end use Exit Sub. I made the change top your macro. Try it out and let me now the results.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    09-21-2012
    Location
    Santa Ana, CA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Excell 2007 VBA to change to a different drive and save file name made up of cells

    Ok I did that sugestion and that worked but now it is not changing the drive to P: and the file name is not being populated. Any ideas. If I use just below code:

    Sub Save()

    fileSavename = Application.GetSaveAsFilename(Range("I3") & "-" & Range("B3") & ".xlsm")
    If VarType(fileSavename) = vbBoolean Then Exit Sub

    ActiveWorkbook.SaveAs Filename:=fileSavename, FileFormat:= _
    xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

    End Sub


    It populates the file name correctly but when I add the ChDir statment it doesn't change drive and directory and the file name is not being populated anymore.

  4. #4
    Registered User
    Join Date
    09-21-2012
    Location
    Santa Ana, CA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Excell 2007 VBA to change to a different drive and save file name made up of cells

    I have also tried the following code just to see what happens and it is not changing the drive and directory.

    Sub TEST()
    Dim sPath As String
    ChDir "P:\CEI Job Lists"
    sPath = ActiveWorkbook.Path
    MsgBox sPath

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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