Excel help please?

[BOAC]

Win XP SP2 and Excel 2000. All Excel files (.pxl) I transfer and convert from my IPAQ PDA to .xls on the desktop and then 'save' appear to have 'macros' in them, although I have not written any. With 'security' at 'medium' I get a warning of macro presence every time I try to open a 'saved' spreadsheet which has come from the IPAQ. If I just open them without saving there is no macro warning, and new Excel sheets generated on the desktop do not do it either.

Are there any settings I have to adjust? It is causing problems putting the worksheets back, as they return to the IPAQ as Pocket Excel 'place holders' with no contents. I'm certain this is a relatively new phenomonen.

[LD Max]

Phantom Macros

First, I have no knowledge of the PDA version(s) of Excel.

However, if you are getting a macro warning when opening the file in XP, it is because there is some Visual Basic module lurking in the file. The module can contain macros, but even if the module is empty its mere existence will give this warning when opening the file.

To remove the warnings, you need to remove the module(s) as follows:

Caution: Removing VB modules may remove some functionality from the file. It may be specific to the iPaQ - I don't know... but I might suggest you work on a COPY of the file first.

Use <Alt-F11> to open the visual basic editor.

Use <Ctrl-R> to open the Project Browser in the editor window.

Look for any folders in the Project Browser other than the folder called "Microsoft Excel Objects" (which contains your worksheets).

The culprit will probably be called the Module folder.

Expand the folder and select any module within it. Right click on each module and select "remove module(x)" from the drop down menu.

Repeat until all the modules are deleted and the containing folder should disappear also.

If you're curious, you can right click on each module and examine the code to see what information it contains before deleting it.

Once you've removed all the modules, quit the VB editor and return to the spreadsheet.

Save the spreadsheet.

Now, when you re-open the spreadsheet, it should launch without those troublesome warnings.

Regards

[Keef]

Sounds a little odd. My Ipaq/Win XP certainly doesn't do that. I can transfer stuff between Pocket XL and full Excel with little bother - except that formatting goes all to pot.

Have you looked at the contents of those macros when they are on the desktop machine? It may be a setting on one or the other that has unexpected effects.

I assume you are using ActiveSynch to keep the Ipaq and the desktop in synch?

Want me to e-mail you an Excel file that started life as a .pxl, to see what it does?

[BOAC]

LD Max - that should keep me quiet for a few days . Thank you for the comprehensive reply.

Keef - thank you also - I will do. Yes - Activesync 4.10 IPAQ 3870

[BOAC]

Files duly dispatched to Keef

LD Max:

I see only Microsoft Excel Objects and a sub called ThisWorkbook as expected and all looks 'ok', but this file (saved from an IPAQ transfer with a new name) still gives macro warning whereas the original (not saved, just transferred) does not.

[Keef]

I've had a look at the files, and BOAC's diagnosis is spot on. Between the Ipaq and the PC, they are getting "Macroised". It's a VB macro that can't be removed, and it screws the file up for subsequent use on the Ipaq.
Google, my friend, found a probable answer. Sadly, the location of the answer isn't one that can be "URL'ed" - or not by me, anyway, so here it is:

Originally Posted by dicky109 on Ozgrid

It was conversion settings in ActiveSync that was screwing everything up.

While I knew that you could insert Worksheets, I didn't realize there were different types, i.e. Worksheet, MS Excel 4 Macro. When I created a new worksheet and copied all the data, formulas & text from the old one, it worked perfectly. This led me to investigate in the Properties menu and finding the "Contents" tab, where it listed the existing sheets as "Excel 4.0 Macros" and the new sheet as "Worksheet", which is what I chose when inserting it. I opened & closed a couple of times to confirm that all was well, then synched the IPAQ, after which, the new sheet wouldn't work again. Upon checking "Contents", I saw that all sheets were now "Excel 4.0 Macros".

The light bulb went off in my addled mind and I realized that ActiveSynch must be converting to the Macro form. Upon checking the synchronization settings, the reinstall set Desktop to Pocket PC to convert to Pocket Excel format *.pxl or *.pxt extensions, which was supposed to convert back to *.xls when going from Pocket PC to Desktop. Whatever was happening, that wasn't it, so I turned off the desktop to Pocket PC conversions and set the Pocket PC to Desktop conversion to *.xls and everything works fine.

[BOAC]

Well, I have fiddled with the PDA to Desktop settings to no avail. ! have now downloaded and run ActiveSync 4.2 with no change. The files are definitely coming over from the PDA with an Excel 4.0 macro in them.

I may be forced to revert to an earlier ActiveSync as Keef says.

EDIT: Somewhat more cheering news - I have discovered that if I save on the PDA as 'Excel97/2000 Workbook' rather than as the default 'Pocket Excel Workbook' the sheet transfers WITHOUT the Macro.....................



standby......................so far so good............................

.....and sorted!!!! Thanks for everyones' time.

[LD Max]

Bizzare... but glad you found a workaround. :-)

[BOAC]

Yes - that, as they say, 'is the joy of Windows'