i get this error even after pasting in tried and true code. I am trying to
paste this code in a workbook I got from someone else. I am not sure why it
doesn't work (something disabled?)

seems like it doesn't like "Visual Basic for Applications" functions.

what do i need to do to make this work?

THANKS!!

Melanie

When in the VBE go to ToolsReferences and see if any references are marked
as missing.

You may want to post your code here for trouble-shooting.


Gord Dibben MS Excel MVP

On Mon, 17 Aug 2009 12:33:01 -0700, Melanie
wrote:

i get this error even after pasting in tried and true code. I am trying to
paste this code in a workbook I got from someone else. I am not sure why it
doesn't work (something disabled?)

seems like it doesn't like "Visual Basic for Applications" functions.

what do i need to do to make this work?

THANKS!!

I think I got this from you! It works in one of my other workbooks. Just
not from a workbook I got from someone else. It gives me an error at the
"Right" function. If I delete this statement, it stops at the "environ"
function.

Private Sub Workbook_Open()
Dim sFile As String
Dim sText
Dim ff As Long

sFile = Application.DefaultFilePath
' or maybe
'sFile = ThisWorkbook.Path
If Right$(sFile, 1) < "\" Then sFile = sFile & "\"

sFile = sFile & "logTest.txt"

sText = Environ("Username") & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")
'Environ("Username") is the login name of user opening the workbook
ff = FreeFile
Open sFile For Append As #ff
Print #ff, sText
Close #ff
End Sub



"Gord Dibben" wrote:

Melanie

When in the VBE go to ToolsReferences and see if any references are marked
as missing.

You may want to post your code here for trouble-shooting.


Gord Dibben MS Excel MVP

On Mon, 17 Aug 2009 12:33:01 -0700, Melanie
wrote:

i get this error even after pasting in tried and true code. I am trying to
paste this code in a workbook I got from someone else. I am not sure why it
doesn't work (something disabled?)

seems like it doesn't like "Visual Basic for Applications" functions.

what do i need to do to make this work?

THANKS!!

Did you check for any missing references?

I tested with this line remmed out

If Right$(sFile, 1) < "\" Then sFile = sFile & "\"

Code still works properly in my setup............from any workbook opened
from any folder.

You are saying that when you delete that line you get an error at

sText = Environ("Username") & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")


Gord

On Tue, 18 Aug 2009 06:09:16 -0700, Melanie
wrote:

I think I got this from you! It works in one of my other workbooks. Just
not from a workbook I got from someone else. It gives me an error at the
"Right" function. If I delete this statement, it stops at the "environ"
function.

Private Sub Workbook_Open()
Dim sFile As String
Dim sText
Dim ff As Long

sFile = Application.DefaultFilePath
' or maybe
'sFile = ThisWorkbook.Path
If Right$(sFile, 1) < "\" Then sFile = sFile & "\"

sFile = sFile & "logTest.txt"

sText = Environ("Username") & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")
'Environ("Username") is the login name of user opening the workbook
ff = FreeFile
Open sFile For Append As #ff
Print #ff, sText
Close #ff
End Sub



"Gord Dibben" wrote:

Melanie

When in the VBE go to ToolsReferences and see if any references are marked
as missing.

You may want to post your code here for trouble-shooting.


Gord Dibben MS Excel MVP

On Mon, 17 Aug 2009 12:33:01 -0700, Melanie
wrote:

i get this error even after pasting in tried and true code. I am trying to
paste this code in a workbook I got from someone else. I am not sure why it
doesn't work (something disabled?)

seems like it doesn't like "Visual Basic for Applications" functions.

what do i need to do to make this work?

THANKS!!

I misspoke.

If I rem out the line

If Right$(sFile, 1) < "\" Then sFile = sFile & "\"

The code does nothing.............no error, just nothing.


Gord


On Tue, 18 Aug 2009 07:13:00 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Did you check for any missing references?

I tested with this line remmed out

If Right$(sFile, 1) < "\" Then sFile = sFile & "\"

Code still works properly in my setup............from any workbook opened
from any folder.

You are saying that when you delete that line you get an error at

sText = Environ("Username") & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")


Gord

On Tue, 18 Aug 2009 06:09:16 -0700, Melanie
wrote:

I think I got this from you! It works in one of my other workbooks. Just
not from a workbook I got from someone else. It gives me an error at the
"Right" function. If I delete this statement, it stops at the "environ"
function.

Private Sub Workbook_Open()
Dim sFile As String
Dim sText
Dim ff As Long

sFile = Application.DefaultFilePath
' or maybe
'sFile = ThisWorkbook.Path
If Right$(sFile, 1) < "\" Then sFile = sFile & "\"

sFile = sFile & "logTest.txt"

sText = Environ("Username") & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")
'Environ("Username") is the login name of user opening the workbook
ff = FreeFile
Open sFile For Append As #ff
Print #ff, sText
Close #ff
End Sub



"Gord Dibben" wrote:

Melanie

When in the VBE go to ToolsReferences and see if any references are marked
as missing.

You may want to post your code here for trouble-shooting.


Gord Dibben MS Excel MVP

On Mon, 17 Aug 2009 12:33:01 -0700, Melanie
wrote:

i get this error even after pasting in tried and true code. I am trying to
paste this code in a workbook I got from someone else. I am not sure why it
doesn't work (something disabled?)

seems like it doesn't like "Visual Basic for Applications" functions.

what do i need to do to make this work?

THANKS!!

I checked and yes there was a missing library. I unchecked it because I
don't think it is important for my purposes.
Now I get an error at the FORMAT statement saying that it has "wrong number
of arguments or invalid property assignment"

"Gord Dibben" wrote:

Did you check for any missing references?

I tested with this line remmed out

If Right$(sFile, 1) < "\" Then sFile = sFile & "\"

Code still works properly in my setup............from any workbook opened
from any folder.

You are saying that when you delete that line you get an error at

sText = Environ("Username") & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")


Gord

On Tue, 18 Aug 2009 06:09:16 -0700, Melanie
wrote:

I think I got this from you! It works in one of my other workbooks. Just
not from a workbook I got from someone else. It gives me an error at the
"Right" function. If I delete this statement, it stops at the "environ"
function.

Private Sub Workbook_Open()
Dim sFile As String
Dim sText
Dim ff As Long

sFile = Application.DefaultFilePath
' or maybe
'sFile = ThisWorkbook.Path
If Right$(sFile, 1) < "\" Then sFile = sFile & "\"

sFile = sFile & "logTest.txt"

sText = Environ("Username") & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")
'Environ("Username") is the login name of user opening the workbook
ff = FreeFile
Open sFile For Append As #ff
Print #ff, sText
Close #ff
End Sub



"Gord Dibben" wrote:

Melanie

When in the VBE go to ToolsReferences and see if any references are marked
as missing.

You may want to post your code here for trouble-shooting.


Gord Dibben MS Excel MVP

On Mon, 17 Aug 2009 12:33:01 -0700, Melanie
wrote:

i get this error even after pasting in tried and true code. I am trying to
paste this code in a workbook I got from someone else. I am not sure why it
doesn't work (something disabled?)

seems like it doesn't like "Visual Basic for Applications" functions.

what do i need to do to make this work?

THANKS!!

I checked and yes there was a missing library. I unchecked it because I
don't think it is important for my purposes.
Now I get an error at the FORMAT statement saying that it has "wrong number
of arguments or invalid property assignment"

I assume you have copied the code directly from the posting and have no
typos.

sText = Environ("Username") & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")

Try experimenting with either of these.................

sText = Application.UserName & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")
sText = "hoohah" & vbTab & Format(Now, "yyyy-mm-dd hh:mm:ss")

If you wish, you could email the workbook to me.

gorddibbATshawDOTca

Change the AT and DOT


Gord

On Tue, 18 Aug 2009 07:50:01 -0700, Melanie
wrote:


I checked and yes there was a missing library. I unchecked it because I
don't think it is important for my purposes.
Now I get an error at the FORMAT statement saying that it has "wrong number
of arguments or invalid property assignment"

i tried both and they still don't work with the same compile error.
i'm pretty sure it has something to do with the workbook... there are no
typos in my code.
unfortunately i can't send this workbook as it's work-confidential. :(
oooh well... thanks anyway.

I should add that if I delete the Format command, it works (but no date)

I thought you said you got rid of the comile error by checking a missing
reference?


Gord

On Tue, 18 Aug 2009 10:27:01 -0700, Melanie
wrote:

i tried both and they still don't work with the same compile error.
i'm pretty sure it has something to do with the workbook... there are no
typos in my code.
unfortunately i can't send this workbook as it's work-confidential. :(
oooh well... thanks anyway.

That I don't understand.

If no typos, Format(Now, "yyyy-mm-dd hh:mm:ss") should work properly.

You do get the logtest.txt file created and the appending of Username works
each time you open the workbook?

Something we're missing but I can't put my finger on it.

Maybe someone else can see the fault?


Gord



On Tue, 18 Aug 2009 10:30:01 -0700, Melanie
wrote:

I should add that if I delete the Format command, it works (but no date)

maybe the Excel workbook is down rev?
even though the Visual Basic for Applications is checked off in the
'References' section, for some reason maybe it isn't there??
i don't know... it's strange because the code works in my other workbooks.
just not this one.

Which version of Excel are you running?

Make a copy of the workbook.

Delete the sheets with sensitive information.

Save the new workbook in any version and send to me.

gorddibbATshawDOTca


Gord

On Tue, 18 Aug 2009 12:03:01 -0700, Melanie
wrote:

maybe the Excel workbook is down rev?
even though the Visual Basic for Applications is checked off in the
'References' section, for some reason maybe it isn't there??
i don't know... it's strange because the code works in my other workbooks.
just not this one.

For the edification of anyone who was follwing this thread..............

Melanie sent me the workbook.....scaled down.

The problem with the Format statement was that she had a module with some
macros.

One of them was Sub Format()

Excel barfed on this until I changed the name to Format22

All is well.


Gord

On Tue, 18 Aug 2009 10:47:57 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

That I don't understand.

If no typos, Format(Now, "yyyy-mm-dd hh:mm:ss") should work properly.

You do get the logtest.txt file created and the appending of Username works
each time you open the workbook?

Something we're missing but I can't put my finger on it.

Maybe someone else can see the fault?


Gord



On Tue, 18 Aug 2009 10:30:01 -0700, Melanie
wrote:

I should add that if I delete the Format command, it works (but no date)