Running Macro Alters Cell Formats RRS feed

  • Question

  • Good afternoon, all -

    I'm developing a macro to aid in automating a process for handling imported csv files.

    I had pretty much gotten it to work and did a final test run, before committing the changes when I noticed all of my Date fields were being displayed as their serial code equivalents. This hadn't been happening in this particular block of code prior to my adding a group of lines to do the actual import from a csv file. But, when I ran the import block on its own, it did fine. Further, it's not only my Date fields that are changing, every cell in the sheet is changing to General format.

    Not only that, but another sheet that handles a second set of imported data is working fine using the very same code; only the file names and sheet names are different. In fact, to handle this second set, I just copied and pasted the code for the first and changed the names, that's it.

    To check it out, I set up some break points to see if I could catch where the format change happened. Looks like it happens where I select the first sheet in the workbook and set the focus to the home cell. Seems odd, since there are no format changes taking place in that code.

    But, if I step through the code line by line, it works fine; all my formats on all sheets are what they should be. The reformatting only happens when I either run the macro in its entirety or when I hit F5 at a breakpoint.

    Any ideas on what's may be happening?

    Thanx in advance for any help!

    Wednesday, June 19, 2019 8:03 PM

All replies

  • Seems I've found the problem - sort of.

    As with another issue I had when importing data, formatting doesn't seem to be maintained when the imported data is actually placed onto the Excel sheet. Well, let me expand on that; the formats are maintained, as far as the actual DATA is concerned, but, once imported, the CELLS retain the default General format. So, for example, any dates that may be formatted as Date by the import query will look like dates (MM/DD/YYYY), but, if you copy and paste them, they will revert to the Excel serial number for that date value.

    That last is what I was doing.

    I haven't found, yet, how to import data to an existing sheet, so I import to a new sheet and then copy its contents to the appropriate existing sheet and then delete the import sheet. After the data was pasted, it was losing the cell formatting, because, even though the destination sheet was formatted properly, the import sheet wasn't, despite the import query specifying data types.

    Anyway, I managed to solve the problem by adding code to my macro to format each column on my import sheet prior to C&P'ing it to the existing destination sheet and the formats were retained. This seems like a step I shouldn't need, because the query defines the formats, but whaddya gonna do. It is what it is and yelling at Excel until I was blue in the face was getting me nowhere.

    I still have no idea why the formats were retained as the query defined them when I stepped through the macro line by line. That's very odd. But, that's neither here nor there.

    Now, I need to figure out how to get the import to populate an existing sheet, rather than creating a new one each time. I can correct for it, but that's inelegant.

    • Edited by Adam Quark Thursday, June 20, 2019 8:54 PM
    Thursday, June 20, 2019 2:15 PM