+ Reply to Thread
Results 1 to 24 of 24

Creating Directory Saving Workbook With Conditions

  1. #1
    Registered User
    Join Date
    08-12-2013
    Location
    Denton, Texas
    MS-Off Ver
    Excel 2010
    Posts
    89

    Creating Directory Saving Workbook With Conditions

    Hello,

    I am trying to code in VBA to Create a Directory using values from cells if the Directory doesn't exist and then save the workbook into that Directory using same values from the cell.
    But if the Directory already exist I would like for the application to skip creating the directory and save the workbook in the existing Directory with the same values.
    The tricky part that I'm on right now is if the workbook already exist I would like to add to the tail end of the name of the file.
    So I guess I'm looking for a loop to determine the next sequence as towhat I can name the file so as to not replace any workbboks in the directory

    Example:
    9001_1.xlsx
    9001_2.xlsx
    9001_3.xlsx

    So the next available number title would be 9001_4.xlsx
    Starting off at 9001_1.xlsx it would loop through trying to save and everytime the value came up <> "" then it would move on to the next untill the value = "" and then saved (I hope I explained that right)

    Please let me know if I wasn't clear enough. An example of my code is as far as I have gotten. I guess I just really need the last loop part. And if anyone may have any recomendations to make it go faster or a better structure, I am all for it. Thank you

    Please Login or Register  to view this content.
    Last edited by jquintana83; 10-20-2014 at 02:07 PM.

  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: Creating Directory Saving Workbook With Conditions

    Hello jquintana83,

    I have code to increment file names that have sequence numbers and can adapt it to your needs but I need more information.

    Can you provide a few examples of what the text in cell "A1" looks like?
    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
    08-12-2013
    Location
    Denton, Texas
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Creating Directory Saving Workbook With Conditions

    Hello Leith Ross,

    It's a pretty simple 12 digit set of numbers
    Example:
    123456789123
    991234567890
    981234567890

    But when saved I would like it to increase like this...

    991234567890_1.xlsx
    991234567890_2.xlsx
    991234567890_3.xlsx

    I hope that helps.
    Thanks in advance!

  4. #4
    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: Creating Directory Saving Workbook With Conditions

    Hello jquintana83,

    Do you have multiple file names in column "A" or just in cell "A1"?

    I see your in Denton, Texas. I have a friend who teaches electronics in Denton.

  5. #5
    Registered User
    Join Date
    08-12-2013
    Location
    Denton, Texas
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Creating Directory Saving Workbook With Conditions

    Hello Leith Ross,

    It's just in Range("A1")
    Really, wow small world?
    Does he teach at UNT?
    I would love to dive into electronics but as of right now I'm still learning the software part (Vb.Net, VBA C#)

  6. #6
    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: Creating Directory Saving Workbook With Conditions

    Hello jquintana83,

    Okay, just one file name in cell "A1".

    He teaches at Denton High School Advanced Technology Complex.

  7. #7
    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: Creating Directory Saving Workbook With Conditions

    Hello jquintana83,

    One more question. In your code it looks like you are creating a folder (if needed) with the value in "A1". Is that correct?

  8. #8
    Registered User
    Join Date
    08-12-2013
    Location
    Denton, Texas
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Creating Directory Saving Workbook With Conditions

    Hello Leith Ross,

    Oh yes good catch and question. Yes, you'll have to forgive my sloppy code but I was just in the middle of revising everything before I ran into the road-block.

    Basically yes, If there is no Dir in the path designated path then I want it to create the Dir using the same value. THere is other criteria that will determine the Dir name but I can write that part of the code.
    So Dir would be C:\Users\Jason\Desktop\991234567890 and in it would be 991234567890_1.xlsx 991234567890_2.xlsx 991234567890_3.xlsx... and so on.


    Thanks for the question.

  9. #9
    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: Creating Directory Saving Workbook With Conditions

    Hello jquintana83,

    Here is the updated macro. Rather than use the Dir method, this macro uses the Shell Application object to return a filtered list of the files.

    I ran this on my system and it worked. Try it out and let me know what your results are.

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    08-12-2013
    Location
    Denton, Texas
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Creating Directory Saving Workbook With Conditions

    Hello Leith Ross,

    Unfortunately I received an error on this line:

    ActiveWorkbook.SaveAs Filename:=dFault_path & "\" & NewName

    I had found this code snippet online and it's pretty close to what I'm looking for except I don't have control of making a new Dir if it doesn't exist. It's locked to saving the .xlsm workbook to the original path but I need to save the workbook as .xlsx in Dir made by value

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    08-12-2013
    Location
    Denton, Texas
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Creating Directory Saving Workbook With Conditions

    Hello Leith Ross,

    This is what I'm getting as far the error goes.

    Run-time error '1004'

    This file could not be accessed. Try one of the following:

    Make sure the specified folder exists
    Make sure the folder that contains the fule is not read-only
    Make sure the file name does not contain any of the following chacracters...

  12. #12
    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: Creating Directory Saving Workbook With Conditions

    Hello jquintana83,

    Since the macro code functioned correctly for me, I would like you to post the workbook that you have the macro in. Obviously, you are doing something that I wasn't.

  13. #13
    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: Creating Directory Saving Workbook With Conditions

    Hello jquintana83,

    Since the macro code functioned correctly for me, I would like you to post the workbook that you have the macro in. Obviously, you are doing something that I wasn't.

  14. #14
    Registered User
    Join Date
    08-12-2013
    Location
    Denton, Texas
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Creating Directory Saving Workbook With Conditions

    Ok, attached is the Workbook... I wonder what I was doing wrong. Thanks for all your help
    Attached Files Attached Files

  15. #15
    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: Creating Directory Saving Workbook With Conditions

    Thanks for the workbook. It just occurred to me. If the file does not have a sequence number initially like 99805501_1, the macro will fail.

    Did you want to add "_1" if 99805501 already exists?

  16. #16
    Registered User
    Join Date
    08-12-2013
    Location
    Denton, Texas
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Creating Directory Saving Workbook With Conditions

    Yes, I think...

    What is happening is when saving the file, it should be grabbing the the value in A1 and adding the string "_" + 1 to keep the files going when saving. As to not erase or hit an error. Kind of exactly how that one code snippet does it that I posted.
    Please Login or Register  to view this content.
    It's adding _V + 1 every time it comes across a file that already allocated the file name.

    I hope this makes sense. Check it out and you'll see what I'm talking about (the code snippet I posted) It's pretty generic and will work without any additional setup. (that snippet saves to the path of the of the workbook)

  17. #17
    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: Creating Directory Saving Workbook With Conditions

    Hello jquintana83,

    Okay, I revised the macro to save the file name without a sequence number. If the file is saved agaimn then a sequence number will be appended starting with "_1".
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    08-12-2013
    Location
    Denton, Texas
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Creating Directory Saving Workbook With Conditions

    Dir.jpg

    Ok so I had to change the dPath (for obvious reasons) and it worked (kind of).... It created the Dir but if I hit the F5 again it gives me this error.
    It should technically just keep saving file after file in that directory only adding the "_" + 1 to the end of the file. But it doesn't. I just get an Overflow error as shown in the jpeg attachment of the screenshot. On the Desktop you can see it created "INSP_99000..." but gets me on that error.

    I need to grab some tacos right now, haha let me know if I missed something. Because when I can't solve a problem, tacos are the only cure

    Thanks

  19. #19
    Registered User
    Join Date
    08-12-2013
    Location
    Denton, Texas
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Creating Directory Saving Workbook With Conditions

    Dir.jpg
    Upload

  20. #20
    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: Creating Directory Saving Workbook With Conditions

    Hello jquintana83,

    I think I need some tequila. It appears that your jpg files did not attach. You should try it again.

  21. #21
    Registered User
    Join Date
    08-12-2013
    Location
    Denton, Texas
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Creating Directory Saving Workbook With Conditions

    Whoa...i have no idea why but it worked...I need to play with it a little bit more when I get back home (at work right now) but I think it solves my problem. I must have ran the code incorrectly last night or swapped something out by mistake or perhaps the Tacos were the cure haha.

  22. #22
    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: Creating Directory Saving Workbook With Conditions

    Hello jquintana83,

    My guess is the tacos. let me know how it goes to when you run it at home.

  23. #23
    Registered User
    Join Date
    08-12-2013
    Location
    Denton, Texas
    MS-Off Ver
    Excel 2010
    Posts
    89

    Re: Creating Directory Saving Workbook With Conditions

    Hey Leith Ross,

    Yep! It's working swimmingly! Ha-Ha thank you. I need to modify a few more things because there is more that attaches to the code. So I'm going to start hacking away at it. I'm going to mark this as solved but I may need your help later depending on what I'm adding to, if that's ok. I usually don't know what I missed when Im designing a program (or neglected to think of). I know, it is really bad practice not to sketch out your ideas but I can't help myself, I like to dive in. Anyways, thank you so much for working so hard on this. I really appreciate it.

  24. #24
    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: Creating Directory Saving Workbook With Conditions

    Hello jquintana83,

    Any questions you have I will be happy to answer. It is good to know it is finally working. I was getting worried we may not discover the problem. You have made me taco believer!

+ 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. Creating a new workbook, copying sheets & saving workbook - Subscript error
    By amphinomos in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-30-2014, 09:04 AM
  2. [SOLVED] Saving new workbook to the same directory as the source workbook
    By drrazor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-26-2014, 10:42 AM
  3. Replies: 3
    Last Post: 09-08-2012, 07:52 PM
  4. 2 questions - Saving log to same directory & amending chart in linked workbook. :)
    By Barry777 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-25-2011, 12:11 PM
  5. Saving Workbook in newly created directory
    By sgreni in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-19-2009, 11:42 AM

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