+ Reply to Thread
Results 1 to 11 of 11

Create and Save Workbook to Current Directory Using VBA

  1. #1
    Registered User
    Join Date
    07-23-2013
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    7

    Create and Save Workbook to Current Directory Using VBA

    Hi,

    I am new to VBA. I am trying to create a code that will copy a range from one worksheet (Unit Mix), paste it into a new workbook as values only, then save that work book under the name Unit Mix to the same directory as the original workbook. Currently, it appears to be working except for the fact that is saves the new workbook to my documents, rather than the current directory of the original workbook. Here is what I have so far:

    Please Login or Register  to view this content.
    Last edited by arlu1201; 07-23-2013 at 12:49 PM.

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Create and Save Workbook to Current Directory Using VBA

    Try changing the end of your string variable to & "\Unit Mix.xlsm"

    Also it won't have a path if the first workbook hasn't been saved yet.
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  3. #3
    Registered User
    Join Date
    07-23-2013
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Create and Save Workbook to Current Directory Using VBA

    I believe this is what you suggested--
    Please Login or Register  to view this content.
    However, when I try this I receive the following error message:

    Run-time error '1004':
    This file extension can not be used with the selected file type.

    If I leave off the .xlsm, it saves the folder to my C drive.

    The first workbook is saved to a specific directory, but this file will be used as a template and will be moved around to different directories, so I would like to find a way to make sure that the new workbook I am creating will follow this original workbook whereever it is moved.

    Thank you!!
    Attached Images Attached Images
    Last edited by arlu1201; 07-23-2013 at 12:50 PM.

  4. #4
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Create and Save Workbook to Current Directory Using VBA

    On a side note. Please use code tags. To use code tags highlight your code and press the # button in the tool bar

    Change:
    Please Login or Register  to view this content.
    To:
    Please Login or Register  to view this content.
    Edit:

    I just realized you don't want to use activeWorkbook because you created a new one. You should qualify which workbook for which you'd like to use the path.
    Last edited by Solus Rankin; 07-23-2013 at 12:32 PM.

  5. #5
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Create and Save Workbook to Current Directory Using VBA

    Amanda,

    Welcome to the forum.

    I have added code tags to both your posts. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  6. #6
    Registered User
    Join Date
    07-23-2013
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Create and Save Workbook to Current Directory Using VBA

    Thank you so much! That worked, I just have one small issue. The name of the original workbook will change frequently since I will be using it as a template and renaming each time. Is there anyway to rewrite it so that it doesnt not specifically reference the original workbook name?

  7. #7
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Create and Save Workbook to Current Directory Using VBA

    Try:
    Please Login or Register  to view this content.
    At the top, then your string becomes:
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    07-23-2013
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Create and Save Workbook to Current Directory Using VBA

    I wasn't clear on exactly where to insert the first part of the code, but tried it at the very top, as well as like this:


    Please Login or Register  to view this content.
    Both ways give me this error:

    Run-time error '13':
    Type mismatch

  9. #9
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Create and Save Workbook to Current Directory Using VBA

    Try it here:

    Please Login or Register  to view this content.
    Note the changes I made in relativePath =

  10. #10
    Registered User
    Join Date
    07-23-2013
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Create and Save Workbook to Current Directory Using VBA

    Thank you!

  11. #11
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Create and Save Workbook to Current Directory Using VBA

    No problem glad to help. If you are satisfied with the solution please mark the thread as solved.

    And if you don't mind adding reputation by clicking the * below my post.

+ 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. Macro: Save Mail- Merged Doc to current Directory
    By Scott Taylor in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-28-2013, 02:30 PM
  2. Create new directory if it doesn't exist and save sheet
    By rkjudy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-01-2011, 01:47 PM
  3. How to get the current directory of the workbook by VBA script?
    By AlexandarRamaiyan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-19-2010, 11:44 AM
  4. Importing text file from current workbook directory
    By hluk in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-11-2008, 06:38 AM
  5. Save to directory and create if not exist
    By Rob in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-29-2005, 02:06 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