Excel Warning Message on New Sheet Created

P

polar

Hi Everybody,

I have an excel workbook with code that generates a separate workboo
with a unique file name.

This is my code to create a new workbook:


Code
-------------------
With Sheets("Invoice2")
Dim invName As String
Sheets("Invoice2").Select
invName = Range("L4").Value & ".xls"
Sheets("Invoice2").Copy
ChDir "C:\Users\Justin\Documents\Razza Jam"

'Will ignore the error
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=invName
On Error GoTo 0

'Will close without another alert message
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End With
End Su
-------------------


Basically, for the Invoice Worksheet, when a button is clicked
separate workbook is generated with just the current invoice worksheet


Everything works fine but when I open the newly created file, I ge
prompted with this message:

"This workbook contains links to other data sources:

* If you update the links, Excel will attempt to retrieve the lates
data.
* If you don't update the links, Excel will use the previou
information."

Then at the bottom of the warning box there is 'update' and 'don'
update' and 'help'.

Is there anyway to eliminate this message coming up each time I creat
a new file? It's just something which is shared and to be honest I woul
be happy if the opened file was read only.

Any help would be greatly appreciated.

Thank you.

Kind regards,

Pola
 
P

Per Jessen

Hi Polar

The new sheet links its values to the original sheet. You can use
PasteSpecial to transform all formulas in the new sheet into values, and
then there will be no link to the original workbook.

Dim invName As String
Dim MyPath As String
MyPath = "C:\Users\Justin\Documents\Razza Jam\"
With Sheets("Invoice2")
invName = .Range("L4").Value & ".xls"
.Copy

'Will ignore the error
On Error Resume Next
ActiveSheet.UsedRange.Copy
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
ActiveWorkbook.SaveAs Filename:=MyPath & invName
On Error GoTo 0

'Will close without another alert message
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End With

Hopes this helps.
....
Per
 
S

Simon Lloyd

This should do what you need

Code
-------------------
Dim ws As Workshee
Dim invName As Strin
With Sheets("Invoice2"
Sheets("Invoice2").Selec
invName = Range("L4").Value & ".xls
Sheets("Invoice2").Cop
For Each ws In ActiveWorkbook.Worksheet
With ws.UsedRang
.Cop
.PasteSpecial xlValue
End Wit
Application.CutCopyMode = Fals
Next w
ChDir "C:\Users\Justin\Documents\Razza Jam
'Will ignore the erro
On Error Resume Nex
ActiveWorkbook.SaveAs Filename:=invName, SetAttr:=ReadOnl
On Error GoTo
'Will close without another alert messag
Application.DisplayAlerts = Fals
ActiveWorkbook.Clos
Application.DisplayAlerts = Tru
End Wit
-------------------

polar;435705 said:
Hi Everybody,

I have an excel workbook with code that generates a separate workboo
with a unique file name.

This is my code to create a new workbook
Code
-------------------
Dim invName As Strin
Sheets("Invoice2").Selec
invName = Range("L4").Value & ".xls
Sheets("Invoice2").Cop
ChDir "C:\Users\Justin\Documents\Razza Jam

'Will ignore the erro
On Error Resume Nex
ActiveWorkbook.SaveAs Filename:=invNam
On Error GoTo

'Will close without another alert messag
Application.DisplayAlerts = Fals
ActiveWorkbook.Clos
Application.DisplayAlerts = Tru
End Wit
End Su -------------------

Basically, for the Invoice Worksheet, when a button is clicked
separate workbook is generated with just the current invoic
worksheet.

Everything works fine but when I open the newly created file, I ge
prompted with this message:

"This workbook contains links to other data sources

* If you update the links, Excel will attempt to retrieve the lates
data
* If you don't update the links, Excel will use the previou
information.

Then at the bottom of the warning box there is 'update' and 'don'
update' and 'help'.

Is there anyway to eliminate this message coming up each time
create a new file? It's just something which is shared and to b
honest I would be happy if the opened file was read only.

Any help would be greatly appreciated.

Thank you.

Kind regards,

Pola
Click to expand...

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com
 
P

polar

Hi Per and Simon,

Thank you for your responses. Unfortunately, I could not get the cod
to work which you gave me Per. Thanks for introducing paste special cod
to me though because I didn't know about that.

Simon, I used your code and I was able to save a new workbook. However
I had to take this line out:


Code
-------------------
SetAttr:=ReadOnl
-------------------


When I put this is on I got a message saying "Compile Error, Argumen
Not Found." Is there a way to eliminate this error message as the rea
only aspect is very useful.

Also, when I open the new file all the cells which were copied ar
selected by highlighting. Is there a way to deselect them and just hav
cell A1 selected without the highlighting?

Thank you again Simon and Per. I really appreciate all of your help.

Kind regards,

Polar
 
S

Simon Lloyd

My apologies, this will work for you!

Code:
--------------------
Dim ws As Worksheet
Dim invName As String
With Sheets("Invoice2")
Sheets("Invoice2").Select
invName = Range("L4").Value & ".xls"
Sheets("Invoice2").Copy
For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
.Copy
.PasteSpecial xlValues
End With
Range("A1").Activate
Application.CutCopyMode = False
Next ws
ChDir "C:\Users\Justin\Documents\Razza Jam"
'Will ignore the error
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=invName, _
Password:="", WriteResPassword:="password", _
ReadOnlyRecommended:=True, CreateBackup:=False
On Error GoTo 0
'Will close without another alert message
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
End With
--------------------


polar;435779 said:
Hi Per and Simon,

Thank you for your responses. Unfortunately, I could not get the code
to work which you gave me Per. Thanks for introducing paste special code
to me though because I didn't know about that.

Simon, I used your code and I was able to save a new workbook. However,
I had to take this line out:
Code:
--------------------

When I put this is on I got a message saying "Compile Error, Argument
Not Found." Is there a way to eliminate this error message as the read
only aspect is very useful.

Also, when I open the new file all the cells which were copied are
selected by highlighting. Is there a way to deselect them and just
have cell A1 selected without the highlighting?

Thank you again Simon and Per. I really appreciate all of your help.

Kind regards,

Polar
Click to expand...


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
 
P

polar

Hi Simon,

That works very well. Thank you very much for that!

Just a quick question, if I was to open the new file as read onl
without the password, is there a way to do that?


Code
-------------------
Password:="", WriteResPassword:="password", _
ReadOnlyRecommended:=True, CreateBackup:=Fals
-------------------


I tried taking out the top line and changing 'ReadOnlyRecommended' t
'ReadOnly' but I had no success. When opened as 'ReadOnlyRecommended'
also get a dialog box which I would prefer not to have.

Thanks again for your reply and code. This is a really terrific forum


Kind regards,

Polar
 
S

Simon Lloyd

It is very difficult to set the read only attribute when you alread
have another workbook open, anyway try this

Code
-------------------
Dim ws As Workshee
Dim invName As Strin
With Sheets("Invoice2"
Sheets("Invoice2").Selec
invName = Range("L4").Value & ".xls
Sheets("Invoice2").Cop
For Each ws In ActiveWorkbook.Worksheet
With ws.UsedRang
.Cop
.PasteSpecial xlValue
End Wit
Range("A1").Activat
Application.CutCopyMode = Fals
Next w
ChDir "C:\Users\Justin\Documents\Razza Jam
'Will ignore the erro
On Error Resume Nex
SetAttr ActiveWorkbook.FullName, vbReadOnl
ActiveWorkbook.SaveAs Filename:=invNam
On Error GoTo
'Will close without another alert messag
Application.DisplayAlerts = Fals
ActiveWorkbook.Clos
Application.DisplayAlerts = Tru
End Wit
-------------------

polar;435848 said:
Hi Simon,

That works very well. Thank you very much for that!

Just a quick question, if I was to open the new file as read onl
without the password, is there a way to do that?
Code
-------------------
ReadOnlyRecommended:=True, CreateBackup:=Fals -------------------

I tried taking out the top line and changing 'ReadOnlyRecommended' t
'ReadOnly' but I had no success. When opened as 'ReadOnlyRecommended
I also get a dialog box which I would prefer not to have.

Thanks again for your reply and code. This is a really terrifi
forum!

Kind regards,

Pola
Click to expand...

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com
 
P

polar

Hi Simon,

Thank you again for your quick response.

Unfortunately, I couldn't get the read only code working. However
it's not a huge issue and I'm grateful for all of your help. Having th
main code and just creating a new workbook is all I need.

Thanks again. Bg:)

Kind regards,

Polar
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

'Save As' Macro Debug Error 6
Excel freezing after sharing document by Email 0
Save workbook without shapes 0
Embedded Excel into Word with Tables 0
Code execution is correct but generates Error :The file you aretrying to open is in a different form 3
Open file password 13
Toggling save warning message on or off 9
Link error/Warning using macro in files converted to Excel 2007 (. 0

Top