I have workbook named "test.xls" which has various sheets.
The workbook is not protected and can be accessed by any
user on the network. I need to log spesific cell information
on a seperate xl workbook automatically (log.xls) without
any indication of the process each and every time before
the "test.xls" workbook is closed.
Here are the columns of "test.xls" that needs to be logged:
Sheet: 14 Cell: A4
Sheet: 14 Cell: A10
Sheet: 15 Cell: A9
Sheet: 12 Cell: A9
---------------------------
How should I code?
TIA

Hi
put the following code in your workbook module. You may have to change
the workbook/worksheet names and also have to adapt the specific
logging ranges to your needs:

-----

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim log_wbk As Workbook
Dim log_wks As Worksheet
Dim last_log_row As Long
Dim path As String
Dim log_filename As String
Dim source_wbk As Workbook
Dim source_wks As Worksheet

'Initialization
Application.ScreenUpdating = False
path = "D:\Temp\" 'change this
log_filename = "logging.xls" 'change this
Set source_wbk = ActiveWorkbook
Set source_wks = source_wbk.Worksheets("Files") 'change this

'check if logging workbook is open / if not open it
On Error Resume Next
Set log_wbk = Workbooks(log_filename)
On Error GoTo 0
If log_wbk Is Nothing Then
Workbooks.Open filename:=path & log_filename
Set log_wbk = Workbooks(log_filename)
End If
Set log_wks = log_wbk.Worksheets("sheet1") 'change this

'get last used row
last_log_row = log_wks.Cells(Rows.Count, "A").End(xlUp).row

'log data - change to your needs
With log_wks
.Cells(last_log_row + 1, 1).Value = Application.UserName
.Cells(last_log_row + 1, 2).Value = Format(Now, "MM/DD/YYYY
hh:mm:ss")
.Cells(last_log_row + 1, 3).Value = source_wks.Range("A1").Value
.Cells(last_log_row + 1, 4).Value = source_wks.Range("B1").Value
.Cells(last_log_row + 1, 5).Value = source_wks.Range("C1").Value
End With


' save the changes
Application.DisplayAlerts = True
log_wbk.Save
log_wbk.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


------


--
Regards
Frank Kabel
Frankfurt, Germany

Martyn wrote:
I have workbook named "test.xls" which has various sheets.
The workbook is not protected and can be accessed by any
user on the network. I need to log spesific cell information
on a seperate xl workbook automatically (log.xls) without
any indication of the process each and every time before
the "test.xls" workbook is closed.
Here are the columns of "test.xls" that needs to be logged:
Sheet: 14 Cell: A4
Sheet: 14 Cell: A10
Sheet: 15 Cell: A9
Sheet: 12 Cell: A9
---------------------------
How should I code?
TIA

Hi
forgot to add some points:
- The logging workbook has to exist - otherwise an error would occur
- The logging workbook should not contain any macros (as this could -
depending on the security settings - cause a warning dialog)
- You may add a check that the maximum row limit of 65536 rows is not
exceeded

On a side-note: Depending oy your company / country this kind of
logging could require that employees are informed about this activity
logging (at least in Germany this would be the case / and employee
delegates have to be informed)!. Sou you may check your specific legal
restrictions for doing this!!


--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank,
Thank you for the code and advise on legal issues which surely I will be
consider...
But need a little bit more explanation on the code if you can.
In your code you have commented for some lines as " 'change this "
referring to my spesific needs. But some confused me a little
(especially this one:
Set source_wks = source_wbk.Worksheets("Files") 'change this)

because the book name and sheets and cells which are going to be used are
stationary.
Thus I'd appreciate if you can use my names. As I have given in my question:
------------------------
The workbook which is going to be tracked is: "test.xls", it resides on:
"C:\"
The sheets of "test.xls" and cells which are to be read and saved from a
Sheet: 14 of "test.xls" Cell: A4
Sheet: 14 of "test.xls" Cell: A10
Sheet: 15 of "test.xls" Cell: A9
Sheet: 12 of "test.xls" Cell: A9
The workbook which'll be used for logging is: "log.xls" and it may reside
on: "D:\Temp"
----------------------------
Hope I've not asked too much.
Thanks a lot
Martyn


"Frank Kabel" wrote in message
...
Hi
forgot to add some points:
- The logging workbook has to exist - otherwise an error would occur
- The logging workbook should not contain any macros (as this could -
depending on the security settings - cause a warning dialog)
- You may add a check that the maximum row limit of 65536 rows is not
exceeded

On a side-note: Depending oy your company / country this kind of
logging could require that employees are informed about this activity
logging (at least in Germany this would be the case / and employee
delegates have to be informed)!. Sou you may check your specific legal
restrictions for doing this!!


--
Regards
Frank Kabel
Frankfurt, Germany

Hi
try the following - not fully tested as I didn't have the same
environment as you have and to be honest i'm a little bit lazy to
re-create it :-)
Put the following code in your test.xls workbook module. Create the
log.xls workbook prior to running this code. You may have to change the
names of your sorce worksheet as I'm not so sure about the names of
these sheets

-----
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim log_wbk As Workbook
Dim log_wks As Worksheet
Dim last_log_row As Long
Dim path As String
Dim log_filename As String
Dim source_wbk As Workbook
Dim source_wks As Worksheet


'Initialization
Application.ScreenUpdating = False
path = "D:\Temp\"
log_filename = "log.xls"
Set source_wbk = ActiveWorkbook


'check if logging workbook is open / if not open it
On Error Resume Next
Set log_wbk = Workbooks(log_filename)
On Error GoTo 0
If log_wbk Is Nothing Then
Workbooks.Open filename:=path & log_filename
Set log_wbk = Workbooks(log_filename)
End If
Set log_wks = log_wbk.Worksheets("sheet1") 'change this

'get last used row
last_log_row = log_wks.Cells(Rows.Count, "A").End(xlUp).row

'log data - change to your needs
With log_wks
.Cells(last_log_row + 1, 1).Value = Application.UserName
.Cells(last_log_row + 1, 2).Value = _
Format(Now, "MM/DD/YYYY hh:mm:ss")
Set source_wks = source_wbk.Worksheets("sheet14")
.Cells(last_log_row + 1, 3).Value = source_wks.Range("A4").Value
.Cells(last_log_row + 1, 4).Value = source_wks.Range("A10").Value

Set source_wks = source_wbk.Worksheets("sheet15")
.Cells(last_log_row + 1, 5).Value = source_wks.Range("A9").Value

Set source_wks = source_wbk.Worksheets("sheet12")
.Cells(last_log_row + 1, 6).Value = source_wks.Range("A9").Value
End With


' save the changes
Application.DisplayAlerts = True
log_wbk.Save
log_wbk.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


------


--
Regards
Frank Kabel
Frankfurt, Germany

Hi,
log.xls and test.xls created but when trying to close test.xls as intended
I get a "Subscript out of range" error on line
---------
Set log_wbk = Workbooks(log_filename)
-----------
will let you know from there on
Ragards
Martyn


"Frank Kabel" wrote in message
...
Hi
try the following - not fully tested as I didn't have the same
environment as you have and to be honest i'm a little bit lazy to
re-create it :-)
Put the following code in your test.xls workbook module. Create the
log.xls workbook prior to running this code. You may have to change the
names of your sorce worksheet as I'm not so sure about the names of
these sheets

-----
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim log_wbk As Workbook
Dim log_wks As Worksheet
Dim last_log_row As Long
Dim path As String
Dim log_filename As String
Dim source_wbk As Workbook
Dim source_wks As Worksheet


'Initialization
Application.ScreenUpdating = False
path = "D:\Temp\"
log_filename = "log.xls"
Set source_wbk = ActiveWorkbook


'check if logging workbook is open / if not open it
On Error Resume Next
Set log_wbk = Workbooks(log_filename)
On Error GoTo 0
If log_wbk Is Nothing Then
Workbooks.Open filename:=path & log_filename
Set log_wbk = Workbooks(log_filename)
End If
Set log_wks = log_wbk.Worksheets("sheet1") 'change this

'get last used row
last_log_row = log_wks.Cells(Rows.Count, "A").End(xlUp).row

'log data - change to your needs
With log_wks
.Cells(last_log_row + 1, 1).Value = Application.UserName
.Cells(last_log_row + 1, 2).Value = _
Format(Now, "MM/DD/YYYY hh:mm:ss")
Set source_wks = source_wbk.Worksheets("sheet14")
.Cells(last_log_row + 1, 3).Value = source_wks.Range("A4").Value
.Cells(last_log_row + 1, 4).Value = source_wks.Range("A10").Value

Set source_wks = source_wbk.Worksheets("sheet15")
.Cells(last_log_row + 1, 5).Value = source_wks.Range("A9").Value

Set source_wks = source_wbk.Worksheets("sheet12")
.Cells(last_log_row + 1, 6).Value = source_wks.Range("A9").Value
End With


' save the changes
Application.DisplayAlerts = True
log_wbk.Save
log_wbk.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


------


--
Regards
Frank Kabel
Frankfurt, Germany

Hi
the log.xls workbook has to be put in the path: D:\temp\ (or you may
change the path variable assignement)

--
Regards
Frank Kabel
Frankfurt, Germany

Martyn wrote:
Hi,
log.xls and test.xls created but when trying to close test.xls as
intended I get a "Subscript out of range" error on line
---------
Set log_wbk = Workbooks(log_filename)
-----------
will let you know from there on
Ragards
Martyn


"Frank Kabel" wrote in message
...
Hi
try the following - not fully tested as I didn't have the same
environment as you have and to be honest i'm a little bit lazy to
re-create it :-)
Put the following code in your test.xls workbook module. Create the
log.xls workbook prior to running this code. You may have to change
the names of your sorce worksheet as I'm not so sure about the names
of these sheets

-----
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim log_wbk As Workbook
Dim log_wks As Worksheet
Dim last_log_row As Long
Dim path As String
Dim log_filename As String
Dim source_wbk As Workbook
Dim source_wks As Worksheet


'Initialization
Application.ScreenUpdating = False
path = "D:\Temp\"
log_filename = "log.xls"
Set source_wbk = ActiveWorkbook


'check if logging workbook is open / if not open it
On Error Resume Next
Set log_wbk = Workbooks(log_filename)
On Error GoTo 0
If log_wbk Is Nothing Then
Workbooks.Open filename:=path & log_filename
Set log_wbk = Workbooks(log_filename)
End If
Set log_wks = log_wbk.Worksheets("sheet1") 'change this

'get last used row
last_log_row = log_wks.Cells(Rows.Count, "A").End(xlUp).row

'log data - change to your needs
With log_wks
.Cells(last_log_row + 1, 1).Value = Application.UserName
.Cells(last_log_row + 1, 2).Value = _
Format(Now, "MM/DD/YYYY hh:mm:ss")
Set source_wks = source_wbk.Worksheets("sheet14")
.Cells(last_log_row + 1, 3).Value = source_wks.Range("A4").Value
.Cells(last_log_row + 1, 4).Value =
source_wks.Range("A10").Value

Set source_wks = source_wbk.Worksheets("sheet15")
.Cells(last_log_row + 1, 5).Value = source_wks.Range("A9").Value

Set source_wks = source_wbk.Worksheets("sheet12")
.Cells(last_log_row + 1, 6).Value = source_wks.Range("A9").Value
End With


' save the changes
Application.DisplayAlerts = True
log_wbk.Save
log_wbk.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


------


--
Regards
Frank Kabel
Frankfurt, Germany

It's already there...
Martyn

"Frank Kabel" wrote in message
...
Hi
the log.xls workbook has to be put in the path: D:\temp\ (or you may
change the path variable assignement)

--
Regards
Frank Kabel
Frankfurt, Germany

Hi
Have you figured out the problem or is the error still there?

--
Regards
Frank Kabel
Frankfurt, Germany

Martyn wrote:
It's already there...
Martyn

"Frank Kabel" wrote in message
...
Hi
the log.xls workbook has to be put in the path: D:\temp\ (or you may
change the path variable assignement)

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank,
No I haven't figured out the problem. It's still there...:(
---
Martyn

"Frank Kabel" wrote in message
...
Hi
Have you figured out the problem or is the error still there?

--
Regards
Frank Kabel
Frankfurt, Germany

Martyn wrote:
It's already there...
Martyn

"Frank Kabel" wrote in message
...
Hi
the log.xls workbook has to be put in the path: D:\temp\ (or you may
change the path variable assignement)

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Marty
to solve this error try the following:
1. Open log.xls before you start this logging macro and see if this
error stil exist
2. Enable screenupdating and insert a breakpoint after opening log.xls
and see if this workbook is really opened. If not probably the workbook
is not found

--
Regards
Frank Kabel
Frankfurt, Germany

Martyn wrote:
Hi Frank,
No I haven't figured out the problem. It's still there...:(
---
Martyn

"Frank Kabel" wrote in message
...
Hi
Have you figured out the problem or is the error still there?

--
Regards
Frank Kabel
Frankfurt, Germany

Martyn wrote:
It's already there...
Martyn

"Frank Kabel" wrote in message
...
Hi
the log.xls workbook has to be put in the path: D:\temp\ (or you
may change the path variable assignement)

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank,
Wow,...if I open log.xls before I start the logging macro everything works
smootly.
But I don't want the user to notice "log.xls "beeeing accesed neither before
start using "test.xls" nor after.
Martyn

"Frank Kabel" wrote in message
...
Hi Marty
to solve this error try the following:
1. Open log.xls before you start this logging macro and see if this
error stil exist
2. Enable screenupdating and insert a breakpoint after opening log.xls
and see if this workbook is really opened. If not probably the workbook
is not found

--
Regards
Frank Kabel
Frankfurt, Germany

O.K. we are getting there.
So please double-check the following (you may post your results to this
NG):
- How is the path variable defined?
- In which directory is log.xls located

Try changing the first lines as following:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim log_wbk As Workbook
Dim log_wks As Worksheet
Dim last_log_row As Long
Dim path As String
Dim log_filename As String
Dim source_wbk As Workbook
Dim source_wks As Worksheet


'Initialization
'Application.ScreenUpdating = False
path = "D:\Temp\"
log_filename = "log.xls"
Set source_wbk = ActiveWorkbook


'check if logging workbook is open / if not open it
On Error Resume Next
Set log_wbk = Workbooks(log_filename)
On Error GoTo 0
If log_wbk Is Nothing Then
msgbox path & log_filename
Workbooks.Open filename:=path & log_filename
exit sub
Set log_wbk = Workbooks(log_filename)
End If


and check if the log.xls workbook is open. If not something goes wrong
with the opening (wrong path, etc.)





--
Regards
Frank Kabel
Frankfurt, Germany

Martyn wrote:
Hi Frank,
Wow,...if I open log.xls before I start the logging macro everything
works smootly.
But I don't want the user to notice "log.xls "beeeing accesed neither
before start using "test.xls" nor after.
Martyn

"Frank Kabel" wrote in message
...
Hi Marty
to solve this error try the following:
1. Open log.xls before you start this logging macro and see if this
error stil exist
2. Enable screenupdating and insert a breakpoint after opening
log.xls and see if this workbook is really opened. If not probably
the workbook is not found

--
Regards
Frank Kabel
Frankfurt, Germany

Hi,

path variable is defined as String just as you have suggested
log.xls is located in (D:\Temp\)
But
I've tried a lot of alternatives too thus cannot find the cause...
I have tried replacing (D:\Temp\) with (C:\Temp\)
I have tried adding the path to all `log_filename` as well
but the result was the same.

If the log.xls file is not opened perior to execution of The macro, it stops
at this line

Set log_wbk = Workbooks(log_filename)
I am stuck
Martyn


"Frank Kabel" wrote in message
...
O.K. we are getting there.
So please double-check the following (you may post your results to this
NG):
- How is the path variable defined?
- In which directory is log.xls located

Try changing the first lines as following:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim log_wbk As Workbook
Dim log_wks As Worksheet
Dim last_log_row As Long
Dim path As String
Dim log_filename As String
Dim source_wbk As Workbook
Dim source_wks As Worksheet


'Initialization
'Application.ScreenUpdating = False
path = "D:\Temp\"
log_filename = "log.xls"
Set source_wbk = ActiveWorkbook


'check if logging workbook is open / if not open it
On Error Resume Next
Set log_wbk = Workbooks(log_filename)
On Error GoTo 0
If log_wbk Is Nothing Then
msgbox path & log_filename
Workbooks.Open filename:=path & log_filename
exit sub
Set log_wbk = Workbooks(log_filename)
End If


and check if the log.xls workbook is open. If not something goes wrong
with the opening (wrong path, etc.)





--
Regards
Frank Kabel
Frankfurt, Germany

Martyn wrote:
Hi Frank,
Wow,...if I open log.xls before I start the logging macro everything
works smootly.
But I don't want the user to notice "log.xls "beeeing accesed neither
before start using "test.xls" nor after.
Martyn

"Frank Kabel" wrote in message
...
Hi Marty
to solve this error try the following:
1. Open log.xls before you start this logging macro and see if this
error stil exist
2. Enable screenupdating and insert a breakpoint after opening
log.xls and see if this workbook is really opened. If not probably
the workbook is not found

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Martyn,
can you email me your workbooks at frank[dot]kabel[at]freenet[dot]de
I've tried this again and it works for me (open or closed). So I'll
like to take a look at your files :-)


--
Regards
Frank Kabel
Frankfurt, Germany

Martyn wrote:
Hi,

path variable is defined as String just as you have suggested
log.xls is located in (D:\Temp\)
But
I've tried a lot of alternatives too thus cannot find the cause...
I have tried replacing (D:\Temp\) with (C:\Temp\)
I have tried adding the path to all `log_filename` as well
but the result was the same.

If the log.xls file is not opened perior to execution of The macro,
it stops at this line

Set log_wbk = Workbooks(log_filename)
I am stuck
Martyn


"Frank Kabel" wrote in message
...
O.K. we are getting there.
So please double-check the following (you may post your results to
this NG):
- How is the path variable defined?
- In which directory is log.xls located

Try changing the first lines as following:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim log_wbk As Workbook
Dim log_wks As Worksheet
Dim last_log_row As Long
Dim path As String
Dim log_filename As String
Dim source_wbk As Workbook
Dim source_wks As Worksheet


'Initialization
'Application.ScreenUpdating = False
path = "D:\Temp\"
log_filename = "log.xls"
Set source_wbk = ActiveWorkbook


'check if logging workbook is open / if not open it
On Error Resume Next
Set log_wbk = Workbooks(log_filename)
On Error GoTo 0
If log_wbk Is Nothing Then
msgbox path & log_filename
Workbooks.Open filename:=path & log_filename
exit sub
Set log_wbk = Workbooks(log_filename)
End If


and check if the log.xls workbook is open. If not something goes
wrong with the opening (wrong path, etc.)





--
Regards
Frank Kabel
Frankfurt, Germany

Martyn wrote:
Hi Frank,
Wow,...if I open log.xls before I start the logging macro
everything
works smootly.
But I don't want the user to notice "log.xls "beeeing accesed
neither before start using "test.xls" nor after.
Martyn

"Frank Kabel" wrote in message
...
Hi Marty
to solve this error try the following:
1. Open log.xls before you start this logging macro and see if
this
error stil exist
2. Enable screenupdating and insert a breakpoint after opening
log.xls and see if this workbook is really opened. If not probably
the workbook is not found

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank,
Thanks for all your help. I am not getting anyware. Although I've changed
the path to "C:\" and put the "log.xls" file there as your last suggestion,
I keep getting the macro error!. Don't know if you are using a version
better then XL2000 maybe inspide of my Win98SE system+Office2000.
Finally: I need to have the log file opened prior to the execution of the
macro in order to write to the log file.
Wow....what a try!.....
I'll be much obliged to any expert that can pin-point the trouble.
Martyn

Hi
can you put the following line in the Immediate window of the VBA
editor
Workbooks.Open Filename:="C:\log.xls"
and hit ENTER

Does this open the log.xls file?


--
Regards
Frank Kabel
Frankfurt, Germany

Martyn wrote:
Hi Frank,
Thanks for all your help. I am not getting anyware. Although I've
changed the path to "C:\" and put the "log.xls" file there as your
last suggestion, I keep getting the macro error!. Don't know if you
are using a version better then XL2000 maybe inspide of my Win98SE
system+Office2000.
Finally: I need to have the log file opened prior to the execution of
the macro in order to write to the log file.
Wow....what a try!.....
I'll be much obliged to any expert that can pin-point the trouble.
Martyn

Yes it does !.

"Frank Kabel" wrote in message
...
Hi
can you put the following line in the Immediate window of the VBA
editor
Workbooks.Open Filename:="C:\log.xls"
and hit ENTER

Does this open the log.xls file?


--
Regards
Frank Kabel
Frankfurt, Germany

Hi Martyn
now I'm getting desperate :-)
If the path variable is declared as path="C:\"
and the lo´g_filename as
log_filename = "log.xls"

then the code snippet
If log_wbk Is Nothing Then
msgbox path & log_filename
Workbooks.Open filename:=path & log_filename
Set log_wbk = Workbooks(log_filename)
End If

should do. Does this msgbox returns the correct information?. I have
tested this again in my environment and everything works fine - also
took it to another PC - result was also o.k.

--
Regards
Frank Kabel
Frankfurt, Germany

Martyn wrote:
Yes it does !.

"Frank Kabel" wrote in message
...
Hi
can you put the following line in the Immediate window of the VBA
editor
Workbooks.Open Filename:="C:\log.xls"
and hit ENTER

Does this open the log.xls file?


--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank,

I am totally confused too...Also am starting to feel like a fool!
The problem occurs while it's checking if the log file is open and open it
if not...
So the macro is not even getting to the line related with the msgbox...
Problem happens before that...He
--------------
'check if logging workbook is open / if not open it
On Error Resume Next
Set log_wbk = Workbooks(log_filename)
On Error GoTo 0
--------------
The error message I receive is exactly this:
Run-time error '9'
Subscript out of range

And the VBA editor highlights the line

Set log_wbk = Workbooks(log_filename)

Hope we can get somewhere now.
Cheers
Martyn



"Frank Kabel" wrote in message
...
Hi Martyn
now I'm getting desperate :-)
If the path variable is declared as path="C:\"
and the lo´g_filename as
log_filename = "log.xls"

then the code snippet
If log_wbk Is Nothing Then
msgbox path & log_filename
Workbooks.Open filename:=path & log_filename
Set log_wbk = Workbooks(log_filename)
End If

should do. Does this msgbox returns the correct information?. I have
tested this again in my environment and everything works fine - also
took it to another PC - result was also o.k.

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Marty
now i see but this line should not throw an error (as this should be
prevented by the line: 'on error resume next'). As a workaround you may
comment these 3 lines (if you're sure that the log.xls file is not
opened manually by a user). Your code should work then. But I'm still
curious why this error is not catched by the 'on error resume next'
line.

--
Regards
Frank Kabel
Frankfurt, Germany

Martyn wrote:
Hi Frank,

I am totally confused too...Also am starting to feel like a fool!
The problem occurs while it's checking if the log file is open and
open it if not...
So the macro is not even getting to the line related with the
msgbox... Problem happens before that...He
--------------
'check if logging workbook is open / if not open it
On Error Resume Next
Set log_wbk = Workbooks(log_filename)
On Error GoTo 0
--------------
The error message I receive is exactly this:
Run-time error '9'
Subscript out of range

And the VBA editor highlights the line

Set log_wbk = Workbooks(log_filename)

Hope we can get somewhere now.
Cheers
Martyn



"Frank Kabel" wrote in message
...
Hi Martyn
now I'm getting desperate :-)
If the path variable is declared as path="C:\"
and the lo´g_filename as
log_filename = "log.xls"

then the code snippet
If log_wbk Is Nothing Then
msgbox path & log_filename
Workbooks.Open filename:=path & log_filename
Set log_wbk = Workbooks(log_filename)
End If

should do. Does this msgbox returns the correct information?. I have
tested this again in my environment and everything works fine - also
took it to another PC - result was also o.k.

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank,
Funny but when I comment these 3 lines everything worked smootly.
The msgbox indicated the log path & name OK.
The macro saved the content to the log file.
Thanks for all the info and patience with me.:)
Martyn

"Frank Kabel" wrote in message
...
Hi Marty
now i see but this line should not throw an error (as this should be
prevented by the line: 'on error resume next'). As a workaround you may
comment these 3 lines (if you're sure that the log.xls file is not
opened manually by a user). Your code should work then. But I'm still
curious why this error is not catched by the 'on error resume next'
line.

--
Regards
Frank Kabel
Frankfurt, Germany

Hi
finally :-)
though I'm still curious

--
Regards
Frank Kabel
Frankfurt, Germany

Martyn wrote:
Hi Frank,
Funny but when I comment these 3 lines everything worked smootly.
The msgbox indicated the log path & name OK.
The macro saved the content to the log file.
Thanks for all the info and patience with me.:)
Martyn

"Frank Kabel" wrote in message
...
Hi Marty
now i see but this line should not throw an error (as this should be
prevented by the line: 'on error resume next'). As a workaround you
may comment these 3 lines (if you're sure that the log.xls file is
not opened manually by a user). Your code should work then. But I'm
still curious why this error is not catched by the 'on error resume
next' line.

--
Regards
Frank Kabel
Frankfurt, Germany