+ Reply to Thread
Results 1 to 11 of 11

Save As to a specific file path...

  1. #1
    Registered User
    Join Date
    11-17-2016
    Location
    Chicago
    MS-Off Ver
    2007
    Posts
    7

    Save As to a specific file path...

    I have this code which works great. It allows me to save the current workbook as a copy with a new name...that name being info taken from certain cells in the workbook. The problem I am having is that it goes to the default documents file path. How would I go about getting this code to save to a specific file path?

    Currently it goes to C:\Users\me\Documents
    I would like it to go to a specific folder in onedrive.


    Please Login or Register  to view this content.
    Any help would be great!
    Last edited by 7hc784hdi; 04-06-2017 at 09:28 PM.

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Save As to a specific file path...

    Hello 7hc784hdi,


    Thank for adding Code Tags.

    Regards
    Last edited by Winon; 04-07-2017 at 05:29 AM.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,877

    Re: Save As to a specific file path...

    Like this.

    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  4. #4
    Forum Contributor
    Join Date
    11-15-2012
    Location
    New York City
    MS-Off Ver
    Excel 2010
    Posts
    224

    Re: Save As to a specific file path...

    I read bakerman2's post with some degree of incredulity, because it seemed to me that the constants string "Path to onedrive" would not work.

    Here's what I put together:

    Please Login or Register  to view this content.
    Sho 'nuff I got the (expected) Error 76 Path not found. However, on both of the following attempts (using the https:\\ prefix), I get Error 52 Bad file name or number

    I, too, have a need to do this, and the path name from retrieving my spreadsheet is a URL:

    https://d.docs.live.net/b5a1d42f6989ea7f/Software Development/Excel Projects/NAG National Open Days

    Sidebar
    Now, I believe that while Windows accepts a slash "/" as a directory separator, the actual directory separator character is a backslash "\" (Hex 0x5C) Which is a whole different wrinkle on the problem. I don't know why Windows returns a forward slash rather than a backslash, but I'll leave that for now...
    End Sidebar

    The underlying point is how do I correctly (as in, it will work) open a file on OneDrive (preferably a new file, opened for output, since that is what I'm looking for.....)?

    Thanks for any pointers,

    Tony

  5. #5
    Forum Contributor
    Join Date
    11-15-2012
    Location
    New York City
    MS-Off Ver
    Excel 2010
    Posts
    224

    Re: Save As to a specific file path...

    OK A lot of Googling later - turns out there are some SDKs for OneDrive.
    However, none of them seem to answer this question: If Excel can work with an arbitrary workbook (and it can, I tried ;-)), why won't VBA do the same? I know that the short-form answer to this is "Because VBA was developed a long time before OneDrive", but really, isn't this the sort of thing to check for when introducing a wonderful new gizmo?

    OneDrive does have a synchronization folder (buried in your Users directory), but VBA reports the web-address of the workbook, rather than the sync directory. Doesn't this make the whole question of synchronizing kind of moot? It shouldn't matter where I make the change, "synchronizing" will cause the same data to be in both locations, right?

    I've just tried a different spreadsheet (with the same need to open a file in the same directory as the actual spreadsheet), and it works fine. The new file goes to the synchronization folder. So it must be something to do with synchronization.

    Continuing to explore...

    Tony

  6. #6
    Forum Contributor
    Join Date
    11-15-2012
    Location
    New York City
    MS-Off Ver
    Excel 2010
    Posts
    224

    Re: Save As to a specific file path...

    Progress!

    I went through OneDrive and forced everything to resync. No problem, now, with opening the 'remote' file. I don't know yet exactly where the file was created (as opposed to being sync'ed), but will post back in a few minutes.

    Tony

  7. #7
    Forum Contributor
    Join Date
    11-15-2012
    Location
    New York City
    MS-Off Ver
    Excel 2010
    Posts
    224

    Re: Save As to a specific file path...

    OK, I think we have an answer.

    The folder was not synchronizing for the first few attempts. I forced resunc, and now it's OK. The file is created locally (in the shadow directory on my computer, not on the cloud directory. The file shows up in both locations, however, and is closed properly when I exit the app.

    I guess I need to spend a bit more time learning how OneDrive synchronization works. Bottom line, makecertain that any new folder is forced to redsync as soon as possible.

    HTH others!

    Tony

  8. #8
    Registered User
    Join Date
    11-17-2016
    Location
    Chicago
    MS-Off Ver
    2007
    Posts
    7

    Re: Save As to a specific file path...

    Thanks for the input.

    bakerman2 answer is pretty much what i was looking for however...the correct file path opens in the save as window; but file name does not get the info from the cells...it is blank. so close.

  9. #9
    Forum Contributor
    Join Date
    11-15-2012
    Location
    New York City
    MS-Off Ver
    Excel 2010
    Posts
    224

    Re: Save As to a specific file path...

    Hi 7hc784hdi

    Sorry to overwhelm you with all my poroblems, rather than helping you deal with yours!

    Before trying to Save As, you should probably check that the appropriate cells are non-blank. Then you can pop up a message box, or quietly do nothing, if it's necessary.

    Good luck!

    Tony

  10. #10
    Registered User
    Join Date
    11-17-2016
    Location
    Chicago
    MS-Off Ver
    2007
    Posts
    7

    Re: Save As to a specific file path...

    Thanks Tony. The cells are not blank. The code works perfect when letting it go to the default path as in the code I posted. Going with what bakerman2 suggested makes the file path work but not the cell part work.
    Has to be something in the formatting...
    ...or me.

  11. #11
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,877

    Re: Save As to a specific file path...

    What if you try it like this.

    Please Login or Register  to view this content.

+ 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. [SOLVED] If workbook path is specific directory then prompt user to save file
    By BG1983 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2016, 06:56 PM
  2. [SOLVED] Save sheet as PDF with specific file path
    By KEDENNIS in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-05-2016, 07:32 PM
  3. [SOLVED] vba to save document to specific file path
    By shaz0503 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-19-2014, 08:16 PM
  4. Save file with specific name and ask user for the path
    By kammariarun in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-08-2013, 05:19 PM
  5. [SOLVED] Save as specific path and name
    By graiggoriz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-25-2013, 06:40 AM
  6. Replies: 0
    Last Post: 01-27-2013, 12:13 PM
  7. [SOLVED] Save As Dialog Box - Default Path and File Name, User Selects Sub Folder from Default Path
    By christenprochaska in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-12-2012, 05:17 PM

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