Error 1004 when creating new workbooks

Hello,

I am in the process of revising a complex macro that I wrote to translate energy e-bills into a standard format for database import. As part of its initialisation, the macro creates and saves three standardised workbooks: one for bills with a single unit rate, one for bills with a dual unit rate, and one for bills that do not match an existing record in our database. Upon saving the first of these workbooks (line highlighted in red below) the macro generates an Error 1004: application-defined or object-defined error.

I have attached the complete macro - though you won't be able to run it, as I cannot for privacy reasons send you an e-bill to use with it - and the relevant section of code at the bottom of the post.

Thank you in advance for your help.

Notes:

1) While the error is generated by the highlighted line, all lines in the box are performed. This happens even when advancing stepwise in the VB editor.
2) Despite generating the error, the workbook is still properly created and saved with the correct filename.
3) No revision has been made to this section of code, but it worked perfectly in the original version of the macro.
4) The original macro also generates Error 1004 when opening the source workbook. The source workbook filename is "EDF work file original.xls" and its path is valid.
5) The revised macro generates Error 1004 when opening a valid source workbook that has worked with the original macro.

Please Login or Register  to view this content.

EDF NHH macro WIP.xls

You need to specify the entire path to where you want to save, as well as the actual filename and extension. For Example:

Please Login or Register  to view this content.

If not outputting to a network share, I would get the path to the current user's desktop or another special folder, so that anyone else running the macro wont encounter a problem.

Further update: I have been experimenting with moving original and test data and macros into each other's home directories. I obtained the following results:

(NB: "original" is used throughout to refer to the original version of the macro, its home directory and associated data. "Test" refers to the macro as listed in my OP, its home directory and associated data.)

1) Using the original macro on test data in the original directory, the file creation section worked fine without my having to add in the path.

2) When I reversed the process and copied the original macro into the test directory, the macro returned Error 1004 during file creation when used with either test data or original data that had worked in the original directory.

3) Copying the test macro into the original macro's directory returned Error 1004 when used with either test or original data.

4) Adding the path to the original macro causes it to return Error 9 regardless of location.

So, I have fundamentally identical code returning different results based on which spreadsheet it is in and where that spreadsheet is located.

I have uploaded a copy of the original macro for comparison, but the only difference is which cells are reformatted.

Scottish Hydro bill translation macro v5.xls

Originally Posted by XeRo Solus

When a path is not fully qualified, Excel defaults to the directory that the module (the workbook the macro is save in) is located. This is why it will be different if the macro is ran from different files in different directories.

Also, if the dir\folder\ path does not exist it won't create a folder.

Thanks, XeRo, but I'm also getting different results from different files in the same directory.

I'm also not trying to save into a non-existent folder; the new workbooks are saved into the same folder as the raw data file, the path for which is extracted from that file's name. I have verified that this path is correct, and all three workbooks are correctly saved to it despite the error. That's what's perplexing me - I'm getting a fatal error in a line of code that works anyway.

I think I have located the source of the Error 9. Working from XeRo's comment I decided to find out what Excel thought the path was, so I added a new variable to the code:

Please Login or Register  to view this content.

Defining outputpath returns Error 9, so it seems there's something Excel doesn't like about the path. The path in question is "H:\Test junk\Recursive junk\".

Originally Posted by XeRo Solus

What do you mean by obvious directories?

I mean the macro is saved in the directory called Macro, and so on. The directories aren't actually called that, but that's the layout of the structure: macro interacting with data in a subdirectory, saving workbooks to that subdirectory.

Please Login or Register  to view this content.

Will save it as the DRateFile variable as a filename but it will save in the host files directory because no path is defined.

I created a dummy .csv on my desktop and ran the code from your OP and it created 3 new files on my desktop without any errors.

That's how it's meant to work, and how the original does work - three workbooks are created in the same directory as the data file.

Originally Posted by Norie

In this line of code you have a variable 'ans' which is never given a value.

What's it's purpose?

Please Login or Register  to view this content.

It's meant to be the position of the last backslash in fileStr. I have no idea why it uses a different variable, though; I wrote the original macro several years ago, and some of the code was borrowed. I've replaced it with fileStr.

Good point about using the Workbook definition to set the top rows of the two main output workbooks. That's much tidier and is now implemented.

Option Explicit has been added and all previously undefined variables are now defined. NewBook as Object is the only relevant one to the code in hand.

Using the "Set wbFile" command does not fix the problem, but using it to open the source workbook triggers the thing where the debugger creates and saves the three workbooks before returning Error 9. Evidently the Error is occurring at the first use of the Set command, which suggests the issue may be in how I've set that up.

Here's the current revision of the code:

Please Login or Register  to view this content.

Thanks everyone for your assistance so far, by the way.

Got it - it was the two-step solution to deleting the empty cells (select, then delete selection) that came from a recorded macro. Swapped it to a direct reference and it went away.

The spreadsheet now processes completely without generating an error, so I'll close this thread off. Again, thanks everyone for your assistance.