Sub Auto_Open()
'
' Cogen Macro
' Macro recorded 29/07/2002 by Denis Lefebvre
'
' Keyboard Shortcut: Ctrl+c
'
On Error GoTo OpenFile ' Prevents error message, if
not open.
Windows("COGENLOG.XLS").Activate ' Tries to activate workbook,
if open.
On Error GoTo 0 ' Restores normal error
handling
GoTo NextPart ' Skip over opening file if
' successfully switched
window.
OpenFile: ' Should only happen if not
open.
Workbooks.Open Filename:="COGENLOG.XLS" ' Opens the workbook.
NextPart: ' Continue the program
activity.
Windows("COGENLOG.XLS").Activate
Sheets("Log").Select
Range("A1").Select
Selection.EntireRow.Insert
Windows("Alain and Denis.xls").Activate
Sheets("Stats").Select
Range("A1:B1").Select
Selection.Copy
Windows("COGENLOG.XLS").Activate
Sheets("Log").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWorkbook.Save
Windows("Alain and Denis.xls").Activate
ActiveWorkbook.Save
If Worksheets("Stats").Range("C2") = "on" Then Application.Quit
End Sub
This way is a little quirky and hacky, but it works without any
issues.
I could probably find a better way at doing this if I had access to
Excel's Visual Basic help file, but I think this satisfies your needs.
Hope this is what you wanted.
- bookface |
Clarification of Answer by
bookface-ga
on
01 Oct 2002 12:58 PDT
Let's try that again, so that you can copy and paste... apparently the
lines are broken down at 70 characters instead of 80.
Also, you should note that doing an "on error" handler for opening the
file will not bypass the message about the file being open already, as
it is a dialog box with multiple options.
Sub Auto_Open()
'
' Cogen Macro
' Macro recorded 29/07/2002 by Denis Lefebvre
'
' Keyboard Shortcut: Ctrl+c
'
On Error GoTo OpenFile
Windows("COGENLOG.XLS").Activate
On Error GoTo 0 'resume normal error activity
GoTo NextPart
OpenFile:
Workbooks.Open Filename:="COGENLOG.XLS"
NextPart:
Windows("COGENLOG.XLS").Activate
Sheets("Log").Select
Range("A1").Select
Selection.EntireRow.Insert
Windows("Alain and Denis.xls").Activate
Sheets("Stats").Select
Range("A1:B1").Select
Selection.Copy
Windows("COGENLOG.XLS").Activate
Sheets("Log").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWorkbook.Save
Windows("Alain and Denis.xls").Activate
ActiveWorkbook.Save
If Worksheets("Stats").Range("C2") = "on" Then Application.Quit
End Sub
|
Request for Answer Clarification by
dennyll40-ga
on
03 Oct 2002 19:48 PDT
Thank you so much. Now could you tell me why my macro works without
the lines you've added? It works with the two files that I have
mentioned. It is when I tried to recreate this with two new files by
copying the macro, (copy and paste), or typing it by hand, or using
the macro "record" feature of Excel that I cannot get this to work
again. Strange? I am doing the exact same thing with these two new
files and it won't work anymore?? There must be a setting that I
changed when I did the "Alain and Denis.xls" & "cogenlog.xls". If only
I could send you the two files so you could examine them. It is such a
simple macro. Anyways, thanks again. I just wish I knew what is
different in those files. Bye for now. Denis.
|
Request for Answer Clarification by
dennyll40-ga
on
03 Oct 2002 19:51 PDT
Thank you so much. Now could you tell me why my macro works without
the lines you've added? It works with the two files that I have
mentioned. It is when I tried to recreate this with two new files by
copying the macro, (copy and paste, of course changing the filenames
to the new ones), or typing it by hand, or using the macro "record"
feature of Excel that I cannot get this to work again. Strange? I am
doing the exact same thing with these two new files and it won't work
anymore?? There must be a setting that I changed when I did the "Alain
and Denis.xls" & "cogenlog.xls". If only I could send you the two
files so you could examine them. It is such a simple macro. Anyways,
thanks again. I just wish I knew what is different in those files. Bye
for now. Denis.
|
Clarification of Answer by
bookface-ga
on
03 Oct 2002 19:53 PDT
And you're using the same computer for both files?
I suspect it might be the operating system, or the version of Excel.
|
,
2 Comments
)