Macro Error
Posted by Galto on January 14, 2002 6:20 AM
Hi,
I export data from a Crystal Report into Excel, and I need to do a little re-formatting before it's sent on to someone else. When the exported file is opened, however, the default page format is adjusted to 10% of the normal size (sometimes only 1% !). I'm pretty much resigned to the fact that I can't change this, so I'm attempting to use a macro to clean it up quickly.
The goal is to re-format the report so it fits into 1x1 pages and is oriented as landscape. I can do these steps manually, but I have several other re-formatting steps to perform as well (deleting extra columns and rows, changing fonts, etc.).
I'm not much into VB programming, so I'm just recording clicks and keystrokes to create the macro. The columns, rows, and font formatting goes OK, but I always end up with an error message similar to this one when it hits the pont of attemptng to change the page format:
====================
"Run Time Error 1004:
Unable to set the LeftHeader Property of the PageSetup Class"
====================
I've tried testing a Macro ONLY with the page format lines, and the same error comes up, so I know that's where it's failing.
Any ideas? Thanks!
Galto
Posted by bob Umlas on January 14, 2002 6:46 AM
OK, what's the statement it bombs at? When it bombs, you can click the "Debug" button and it'll take you to the offending statement. I need to knw exactly what that line is before I can help!
Posted by Galto on January 14, 2002 7:28 AM
:bombs, you can click the "Debug" button and
:it'll take you to the offending statement. I
:need to know exactly what that line is before I
:can help!
Hi,
The line is the LeftHeader line...
With ActiveSheet.PageSetup
------->.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
I removed that part, then it crashed on the CenterHeader line. I figured it would do the same to each line on the header/footer lines, so I did a little experimenting...
I removed all the Header and Footer sections from the VB and it works now. Now when the macro runs, it flashes the screen several times before finishing. Can I assume it's going through every item on the four "Page Setup" tabs, even though there's nothing else changed? If that's the case, then there's a lot more I can probably remove to reduce the amount of unnecessary code.
Galto
Posted by Jerid on January 14, 2002 12:30 PM
Possible Solution
Do you have a printer installed on that PC? You get that error when you try to change options in page setup and a printer in not setup.
Just a thought.
Jerid
Posted by Galto on January 16, 2002 5:04 AM
Re: Possible Solution
Hi,
Yes, there is a printer connected. I was able to slve the problem already. See Below. Thanks for your time.
