+ Reply to Thread
Results 1 to 19 of 19

Trying to get the filepath on "Open", but get filepath of a temporary file instead

  1. #1
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Trying to get the filepath on "Open", but get filepath of a temporary file instead

    I have a workbook which retrieves data from another workbook via a button. I also want to automatically run this data retrieval process as soon as my workbook opens, so I copied the code into the "Open" property of "ThisWorkbook" in VBA.

    The process takes the filepath of the current workbook and then foes up a directory and into another directory where the other workbook is. But when the code runs as part of the "Open" process, it is giving me a temporary filename and filepath i.e. "C:\Documents and Settings\.....\Temp" for the filepath and "CVT148.tmp" for the filename. But the workbook is open so it should work. I've tried using ThisWorkbook and Activeworkbook, but neither work.

    Does anyone know how I can get around this?

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    Can you post your code??
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    Try this:

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    Quote Originally Posted by mehmetcik View Post
    Try this:

    Please Login or Register  to view this content.
    What should that do?

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    Ian

    Where exactly are you having the problem?

    Is it in the last code you posted or elsewhere?
    If posting code please use code tags, see here.

  7. #7
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    The code I posted above (Sub RetrieveTransferFigures) runs fine when run from a button. But if I set it to run as soon as the workbook is opened (i.e. in the "Open" property of ThisWorkbook), it fails, because the "current" path and filename is that of a temporary file.

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    Where are you referring to the 'current' path and filename?

    I can see you are referring to ThisWorkbook for it's path and name.

    Can you post the exact code you have in the workbook's Open event?

    Also, are you opening the workbook with code or manually?

  9. #9
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    The exact code is the same as above, but surrounded by the "Open Workbook" sub:

    Please Login or Register  to view this content.
    So the idea is that when the user opens the workbook (which contains this code in the ThisWorkbook Microsoft Excel Object), the code runs. It takes the current filepath using the current workbook, goes up a directory, goes into the "DO NOT USE" directory, and opens another workbook, which has the same name as the current workbook, but with " copy" at the end of the filename.

    The code succeeds when run from a button on the spreadsheet, but it fails when it runs when the workbook is first opened, because at that point, the filepath is a temporary directory and filename is a temporary filename, so the code gets confused.
    Last edited by ianpwilliams; 08-19-2013 at 03:16 PM.

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    Have you tried it this way:

    Please Login or Register  to view this content.

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    Have you tried it this way:

    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    No that doesn't work either. Same result. I can tell it fails because I have a cell at the top of the workbook, which refers to another hidden cell, and that hidden cell contains some of the current workbook's filename (to give the workbook page a title based on the filename). But the cell at the top always says #VALUE! when the process runs on opening.

  13. #13
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    Maybe I could put a command in the VBA code to "wait 1 second" or something, if there is such a thing?

    Either that or I could cheat, and put in a dialogue box for the user to click OK, reminding them to to click the data retrieval button.

  14. #14
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    Something like this? There is a Wait function but, to me, it's complicated

    Please Login or Register  to view this content.
    Last edited by xladept; 08-20-2013 at 03:16 PM.

  15. #15
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    Odd, that just makes Excel crash for me.

    I also tried bringing up a MsgBox prompting the user to click OK, then calling RetrieveTransferFigures, but I can see that when the MsgBox comes up, it's still at the point where the filepath and filename are temporary, because the workbook cell still says "VALUE".

    I may have to just resort to bringing up a MsgBox on Open, prompting the user to retrieve the figures using the Retrieve Transfer Figures button in the workbook.

  16. #16
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    Weird - I can't think of anything else

  17. #17
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    Yeah I can't think of anything either. No problem, I can just bring up a message box every time the workbook is opened, reminding the user to click the data retrieval button.

    Thanks for the help anyway!

  18. #18
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    Hey Ian,

    I have a fault in that I often edit my post and, in this case, I did that

    So, take a look and see if you tried it with the code messed up before my edit - I originally had T-Timer which would have caused the system to overload - maybe that bad code was the problem and there was nothing wrong with the concept??

  19. #19
    Forum Contributor
    Join Date
    12-09-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    201

    Re: Trying to get the filepath on "Open", but get filepath of a temporary file instead

    I tried that code, and the bit about waiting 1 second worked. Unfortunately it didn't make any difference! I should have known it wouldn't work really, because the filename isn't set when the process runs, so waiting any amount of time isn't going to help. I'll keep that bit of code though - could be useful in the future!

    I wonder if it might be because I'm using Office 2003? Or maybe just because my computer is old?

    At this stage I have a message box coming up to remind the user to press the button anyway, which is good enough.

+ 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] Need help with filepath in "Query from Excel Files"
    By Planteplankton in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 02-11-2013, 02:24 AM
  2. [SOLVED] Copying files from a selected filepath to a filepath mentioned in a worksheet's cell
    By subbby in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-10-2012, 03:59 PM
  3. Replies: 3
    Last Post: 09-24-2010, 04:04 AM
  4. Replies: 4
    Last Post: 07-19-2010, 09:05 AM
  5. Connection:="TEXT; followed by a parameter for the filepath ???
    By floep in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-22-2006, 08:43 AM

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