auto open

Hello!

I am having problem again.
My problem goes like this..

I can only put the vba code in file a.
In file a, i have a button for me to click so that the file will send a backup to folder 1.

In file a, i want the file control toolbar (cut, paste function) to be enable. And i want the file in folder 1, file b control toolbar to be disable.

I want the marco to auto run when i open the files.
So what code can i use?

the code i am using are:

code

Sub Auto_open()
Call DisableCutAndPaste
End Sub

/code

But how can i differentiate the 2 different file taking for example
file a = 123
file b = test

Please help!Thanks!

Hi woonyah, welcome to the forum! :)

If I understood the problem correctly, I can think of two ways. The first would be simply have the file check its location on Open, to see if it is in "folder 1".
If that isn't reliable because the backups might get moved around, you could set a flag in file a before you create the backup, and remove it again from file a afterwards. So, the backup, when opened, would check for the flag and find it set, the original would check for the flag and find it not set - thus knowing which one they are. I think all of the Office apps have something like a CustomProperty collection which you can use for this purpose, but depending on which app we are talking about there may be alternatives.

Does that help? :)

Erm..what i want was to run a vba code to transfer my file to folder one.
however, in folder one, the toolbar function must be disable, meaning you cannot copy, save nor cut the file..

However, in the orginal file, i want the toolbar function to be enable.
And also, the code would be placed in my orginal file.

Erm..i edited my code to:

code

Private Sub Workbook_open()
If ActiveWorkbook.Name = "test.xls" Then
Call DisableCutAndPaste
ElseIf ActiveWorkbook.Name = "123.xls" Then
Call EnableCutAndPaste
End If
End Sub

/code

do you understand? So sorry if i didnt express it well. I am not good in programming.

Hmmm. I think I understood you, and I think my suggestions address the task. :)
The code would be in both workbooks, surely. When you copy a workbook, you copy the code along with it.

What about the modified code you posted... does it work? If it does, what more is needed? :confused:

ETA: It might help us if you posted the code you're using to copy the workbook, since my second suggestion would require changes there as well as in the _Open event.

I have done it.
I realized my mistake. Sorry!

Now that my thing is done, i have the next problem.
The problem is

is there a way to open excel in a new application each time i open a file?
is there any code?




Please post Excel questions, in the Excel forum.

Thank you.

I'm not sure what you mean by "new application". Excel will open workbooks in a new window but in the same process.

open excel in a new application
135815

Yes. i want to open excel in a new application.
i thought of using this code and put in vba:

code

Function NewWindow() As Window

End Function

code/

but i dont know what to put into the space between the code.

Yes. i want to open excel in a new application.
i thought of using this code and put in vba:

code

Function NewWindow() As Window

End Function

code/

but i dont know what to put into the space between the code.

Hi Woonyah,

When Timbo posted, he gave you a link to read. It tells you how to start a new instance of Excel. Additionally, it's a really good tutorial and covers all of the important things you're going to need to know. ;)

Colin

Hi,
Sorry that day i couldnt open the link. I just see it. Thanks. I will try.

Woonyah

Hi,

I read the thread but i am not too sure. Actually i have come out with the code.

[code]
Sub ExcelInstances()
Dim xlApp1 As Object
Set xlApp1 = CreateObject("Excel.Application")
xlApp1.Visible = True
xlApp1.Workbooks.Open filename:="U:\test\Monthly Accounts Portfolio_123.xls"
End Sub
[code/]

However, now how do i call for this code when i wanna open the file in the folder?

Well, how do you want to call for it? Do you want to run this entirely automatically, or when the user does something (like click a button)?

BTW, the syntax for tags here is ..., so for a code box it would be .... :)

I want to run it automatically.

Which mean when i click on the file in the folder level, that code will run and the file will open in a new excel.

You can't do this directly from the workbook's own open event. You would have to bring a second workbook into the fray, which would actually be the one you open, and from there automate a new Excel instance with the target workbook in. It could get messy. Any solution to this is going to be quite contrived.

If you want every Excel file to be opened in a new instance of Excel then you could change your Excel Application settings:
Tools --> Options --> General Tab --> Tick the "Ignore other applications" option.

Colin

i click on the file in the folder level
Do you mean Excel's open file dialog, or windows explorer? If it's the former, Colin has answered it above. If it's the latter, the question stops being an Excel question altogether.
You can't do this directly from the workbook's own open event.
I suppose it could check for other workbooks in the same application in the open event, and if there are any, it could launch a new application with itself and then close this instance of itself. But, as you said, it's quite contrived.
Tools --> Options --> General Tab --> Tick the "Ignore other applications" option.
Cool, I didn't know that was possible. Most MDI apps won't allow you to do that at all. Thanks! :)

I suppose it could check for other workbooks in the same application in the open event, and if there are any, it could launch a new application with itself and then close this instance of itself. But, as you said, it's quite contrived.


I think the problem with that is that it will open the itself as read-only in the new instance, so you'd have to put up with a read-->write dialog box after it's closed itself in the original instance.