sheet not saving in correct filepath, instead its saving it to My Documents

**dmcgov** · 10-11-2016, 02:22 PM

I have the following code, working except that its not saving the new spreadsheet in the correct folder. (instead it is saving it to My Documents folder). I need it to save in the parent folder of the initial spreadsheet.
I added a line (now commented out) that does save the file in the correct path, but then the macro does a dump and fails further down the line.

Please Login or Register  to view this content.

when i uncomment out this line, the macro fails

Please Login or Register  to view this content.

can anyone help me with this?

**Kenneth Hobson** · 10-11-2016, 02:48 PM

I don't see anything wrong with that. I seldom rely on relative file references. I use full drive:\path\filename.ext paths.

**dmcgov** · 10-11-2016, 03:15 PM

when i put the full path in, the macro fails here:

Please Login or Register  to view this content.

thoughts?

**Norie** · 10-11-2016, 03:16 PM

If you don't specify the path to save file it will be saved in what Excel/VBA sees as the current directory, which may or may not be what you want/think it is.

How does the code fail when you use this?

Please Login or Register  to view this content.

Also, why are you saving the newly added workbook here,

Please Login or Register  to view this content.

then, even though you haven't closed it, re-opening it here,

Please Login or Register  to view this content.

and then again re-opening it.

Please Login or Register  to view this content.

Actually, could you explain what the code is actually meant to do?

Is it meant to save a specific worksheet from the workbook the code is in to a new workbook and add code from the original workbook to the new workbook?

**dmcgov** · 10-11-2016, 03:32 PM

yes this statement is correct: Is it meant to save a specific worksheet from the workbook the code is in to a new workbook and add code from the original workbook to the new workbook?

i have a workbook that contains multiple PO tabs. i need to save them as i use them. so when i put this in my code

Please Login or Register  to view this content.

and that code works, it saves it into the parent directory of the source file.

but when i do that it fails here:

Please Login or Register  to view this content.

**Norie** · 10-11-2016, 03:41 PM

What exactly do you want/not want in the new 'PO' workbook(s)?

**dmcgov** · 10-12-2016, 06:19 AM

so the new workbook should contain just one PO, add code to it, add supporting sheets for the macro to run.

the issue is where to put the "With ThisWorkbook" statements so that they work.

**dmcgov** · 10-12-2016, 06:45 AM

so i made a change to the code, it's failing on Workbooks("Purchase order-Template").Sheets("Formulas").Copy Before:=openworkbook.Sheets(1), gives me a run time error '91', object variable or with block variable not set

Please Login or Register  to view this content.

thoughts?

**TMS** · 10-12-2016, 06:59 AM

Probably missing something here, but the variable abc, as far as I can see contains a sheet name. Where do you add a file extension to the file name of the workbook?

**dmcgov** · 10-12-2016, 07:06 AM

so i fixed it. made the following changes and now it is working as expected.

Please Login or Register  to view this content.

**dmcgov** · 10-12-2016, 07:09 AM

Originally Posted by TMS

Probably missing something here, but the variable abc, as far as I can see contains a sheet name. Where do you add a file extension to the file name of the workbook?

the format is found on this line:

Please Login or Register  to view this content.

which saves it as a .xlsm format

**TMS** · 10-12-2016, 07:14 AM

Might save it with that format, but does it add the extension for you? I've always created the full file path, file name and file extension. Something I must try

Thanks for the rep.

sheet not saving in correct filepath, instead its saving it to My Documents

LinkBack

Thread Tools

Rate This Thread

Display

sheet not saving in correct filepath, instead its saving it to My Documents

Re: sheet not saving in correct filepath, instead its saving it to My Documents

Re: sheet not saving in correct filepath, instead its saving it to My Documents

Re: sheet not saving in correct filepath, instead its saving it to My Documents

Re: sheet not saving in correct filepath, instead its saving it to My Documents

Re: sheet not saving in correct filepath, instead its saving it to My Documents

Re: sheet not saving in correct filepath, instead its saving it to My Documents

Re: sheet not saving in correct filepath, instead its saving it to My Documents

Re: sheet not saving in correct filepath, instead its saving it to My Documents

Re: sheet not saving in correct filepath, instead its saving it to My Documents

Re: sheet not saving in correct filepath, instead its saving it to My Documents

Re: sheet not saving in correct filepath, instead its saving it to My Documents

Thread Information

Users Browsing this Thread

Similar Threads

VBA code not saving into FilePath

[SOLVED] Macro to save to Network Drive is saving to My Documents

[SOLVED] Complex Parsing and Saving as Multiple Text Documents

Word not saving documents correctly

[SOLVED] saving documents with macros

[SOLVED] Saving New Documents on a network Drive.

Saving to users My Documents

Open documents interfering with saving

Bookmarks

Bookmarks

Posting Permissions