Problem with spreadsheet created by Analyze in Excel

[chartee]

I have an Access 97 table that I wanted to analyze in Excel. I used the Analyze in Excel command found under Office Links in Access. Once the data was in Excel I inserted two columns to the left of the table date giving me columns A and B blank. I added dated to those columns and then did a hide columns for A and B. At that point if I use the unhide command I can see the columns again. If I save as an Excel file with the two columns hidden I am unable to unhide them. That makes the first column in my spreadsheet column C. Interestingly, if you select the entire worksheet with columns A and B hidden and paste it to a new workbook, colums A & b are there. If I insert columns in the middle of the worksheet, this doesn't happen. Can anyone tell me what is happening to cause this?

 

[AnnabelleMarie]

I'm not sure that your problem has anything to do with the Analyze command but is simply just a 'hiding columns' problem. If you hide column B, then to unhide it all you have to do is select a cell in column A and column C and Excel knows you must want to unhide column B (whatever is between what you select) and so it does. But, if you hide column A (or in your case A and B), Excel isn't sure which column you want to unhide since you aren't selecting a cell before column A (since there isn't one), so nothing happens. But if you select the entire worksheet and choose unhide columns, it unhides every column. Try experimenting with hiding columns without using column A and see if you have the same problem. I hope this helped.

 

[chartee]

I did try selecting the entire worksheet and then doing the unhide but once it had been saved as an Excel worksheet it would not unhide the two columns that had been hidden.

 

[AnnabelleMarie]

I'm not sure how you are selecting the entire worksheet, but try clicking on the upper left corner square (I'm not sure what you call it) right under the range name box which will highlight the entire worksheet and then select unhide. I just tried it after saving a file with hidden columns in Excel and it worked.

 

[chartee]

I did use the square in the upper left corner to select all of the worksheet. If I do these same actions on a spreadsheet which I started in Excel everything works just like it should. The problem only happens when use the Analyze in Excel command in Access and then same the result as an Excel file.

 

[AnnabelleMarie]

Sorry I misunderstood your problem. I'm going to experiment with this and see if I can replicate your problem. Hopefully we'll figure this out!

 

[chartee]

Thanks much....it is driving me crazy.

 

[GDGarth]

Select the whole spreadsheet and try setting the width to some reasonable number. Maybe the columns are unhidden, but just too narrow to see.......

I'll experiment with mine a little bit.

 

[GDGarth]

OK, it did the same thing in mine.

I selected the entire sheet, and set the column width to 12, and it displayed the columns.

 

[chartee]

The one step I left out was that I first tried to open the hidden columns by double clicking on the line on the right side of the box that selects all. When I did that, the cross changed to an arrow pointing downward.

I reset the column widths after selecting the whole worksheet and then tried to unhide the columns but it didn't work.

 

[AnnabelleMarie]

I'm sorry chartee but I can't replicate your problem. I went into Access 97 and used the Analyze table in Excel command which automatically put the table columns into Excel. I then inserted 2 blank columns (A&B) and added some data to them. I hid them and then saved the file. After I opened it back up, I was able to unhide A & B. I'm using Excel 2000 (not 97), but I did use Access 97.

If I am missing a step here, let me know. Otherwise, I'm sorry I couldn't be of more help. Please feel free to e-mail the file to me (or one like it) just to see if I can unhide the columns. My email is argodwin@msn.com

Maybe someone else can give better insight.

 

[GDGarth]

A better way to fix it is to type the address in the box in the top left, then unhide it. That seems to work, too.

 

[chartee]

I tried that one, too and it didn't work.

 

[chartee]

I just tried that again on the replicated spreadsheet and it worked. However, when I tried it on the user's worksheet who asked the question, it did not work.