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

"Lisa" wrote in message
...
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!!

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
"Lisa" wrote in message
...
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!!

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


---
Message posted from http://www.ExcelForum.com/

Gareth,

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

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

John

"Gareth" wrote in message
...
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
"Lisa" wrote in message
...
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!!

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?
-----Original Message-----
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

"Lisa" wrote in
message
...
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!!



.

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)?

-----Original Message-----
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


---
Message posted from http://www.ExcelForum.com/

.

Here. This will work:

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

--
Message posted from http://www.ExcelForum.com

oops!

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

--
Message posted from http://www.ExcelForum.com

oops again!

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

--
Message posted from http://www.ExcelForum.com

What is the file type? File - SaveAs - SaveAsType


"Lisa" wrote in message
...
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!!