PLEASE stop Excel version popup!!

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

I hope someone can assist me with this problem. In
Access, I am exporting data to an Excel 2000 spreadsheet
then sending via Outlook to users. Everything was working
UNTIL I added code to format the Excel file.

I used:
Worksheets(sheetName).Range("A1:A26").Formula = "='Sheet2'!
$A$1:$Z$1"

Now every time a file is created I get an Excel popup
message stating the file is in Excel 95/97 version and
asks if I would like to overwrite it with the latest
version.

What did I do wrong? How do I stop it? Is there anyway
to fix my code to prevent this or a way to automatically
select 'Yes' to the popup?

Thanks!!
 
Lisa,

I'm not sure if this will work.
I don't have Excel 95 (or 97), but you could try:

Application.DisplayAlerts = False
just before that line of code, and
Application.DisplayAlerts = True
just after it.

Post back and let me know if that did work.

John
 
try this:

Application.DisplayAlerts = False

'your code here

Application.DoisplayAlerts = True

I'm not sure if Yes or No or neither of them is done.

Gareth
 
For x = 1 To 26
Worksheets(sheetName).Cells(x, 1).Value =
Worksheets(“Sheet2”).Cells(1, x).Value
Next x

But that will only do it once. It won’t update Sheet2 automatically. -
Pikus
 
Gareth,
I'm not sure if Yes or No or neither of them is done.
Click to expand...

Neither. The alert never fires at all so nothing is done (which
could be contrued as a no).

John
 
John and Gareth-
Thanks for the help, but it did not work. Any other
suggestions?

I was not receiving the popup before I wrote the
formatting code, so I am beginning to think that the
Worksheet.Range code that I wrote was a 95/97 version of
code and not the proper code for 2000, which changes the
file to a 95/97 version. Could this be?

Any other ideas?
 
Pikus-
Maybe I didn't do this right. I don't think Cells is an
object in the Worksheet class though. I could be wrong
though.

Just in case, explain to me the x and Cell (x, 1). Is
this putting the value of x (which is a value on sheet2)
in cell A1 of sheetName(the current sheet)?
 
Here. This will work:

For x = 1 To 26
y = x + 64
Worksheets(sheetName).Cells(x, 1).Formula = "='Sheet2'!$" & Chr(y)
Next
 
oops!

For x = 1 To 26
y = x + 64
Worksheets(sheetName).Cells(x, 1).Formula = "='Sheet2'!$" & Chr(y)
"1"
Next
 
oops again!

For x = 1 To 26
y = x + 64
Worksheets(sheetName).Cells(x, 1).Formula = "='Sheet2'!$" & Chr(y)
"$1"
Next
 
Back
Top