problem Automation Error

[RoyMakaay1985]

Similar to the problem

here, I receive an automation error, when using this code, the debugger is marking the line with n =...

Please Login or Register  to view this content.

Would be great if someone can help!

[romperstomper]

Do you still get the error if you use:

Please Login or Register  to view this content.

[RoyMakaay1985]

Yes, the error still occurs.

[romperstomper]

Are you running this in Excel? If so, which version?

[RoyMakaay1985]

Yes, in Excel, the Version is 2002, SP3

[romperstomper]

OK, a few more questions:
1. When you used my first correction, did the error still occur on the same line, or did it occur on the m = ... line?
2. How are n and m declared?

[RoyMakaay1985]

1. Now the debugger highlights "With WB.Worksheets(1)"
2. n and m are not declared

[romperstomper]

Weird. Do you have any worksheets (as opposed to chart sheets) in the workbooks? What sort of files are you opening? (Are WB and WB2 declared as Workbooks?)

[RoyMakaay1985]

It surprises me as well.

WB and WB2 are both declared as Workbooks. Both of them are Worksheets filled with data. And both of them are not the file with the vba-code in them. The Chart is supposed to be saved in the GUI, where also the vba-code is.

[romperstomper]

Does this version work:

Please Login or Register  to view this content.

[RoyMakaay1985]

Sorry, have to revise... It still does not work and shows the same error, same line as before

[romperstomper]

I cannot replicate that behaviour. Any chance you could post the files in question for testing?

[RoyMakaay1985]

Here is the full code, this is in the GUI, where the user selects two files (Quelldatei, Quelldatei 2) and then clicks Button11:

Please Login or Register  to view this content.

[romperstomper]

It would appear you did not use the code as I posted it:

Please Login or Register  to view this content.

should be:

Please Login or Register  to view this content.

Note the two periods before the two instances of the word Rows - they must be there.

[RoyMakaay1985]

Hm, you are right about that. However, the error is still in the same line after adding the periods. The fault message, to add that, says "Runtime error '-2147221080 (800401a8)': The method 'Worksheets' for the object 'Workbook' failed"

[romperstomper]

What if you replace Worksheets with just Sheets, as a matter of interest?

[RoyMakaay1985]

When I change it to sheets, the fault message again says 'Automation error'

However i changed the source-code now, running the same line twice now, once for WB, then subsequently for WB2. The error message disappears. But, not all the data I want it to save, is saved... The first run (for WB) is not saved I assume, DaFeld (1, Zaehler) just returns empty cells.

Please Login or Register  to view this content.

[romperstomper]

Your ReDim is destroying the data. Remove the second instance of:

Please Login or Register  to view this content.

[RoyMakaay1985]

Thanks! That helped. now I am just wondering what made the error appear in the first place... Could it be, that excel only allows to use one instance of a Workbook at a time?

[romperstomper]

Nope, that shouldn't be an issue. Is there any code in those workbooks?

[RoyMakaay1985]

No, they're just filled with Data