copy data from one worksheet and paste into another workbook

[Guest]

Hi,
I have a form that I use in excel and I would like to keep track of people
that fill out the form on a seperate (hidden) workbook. I use a command
button that everyone hits to send the form, so somewhere in that code, I
would like to open another workbook, log some data (cells A1, B2, & C4), save
it, and then continue on....all hidden, so the user never sees it.

Any help would be very appreciated. Thanks,

 

[Guest]

wouldn't it be easier simple to append to a log ( text) file....both
extremely fast and totally invisible to the user..

Use the File System Object which is in this library...
set a reference to the scripting runtime dll in the IDE ( Tools/references...)

Sub Logger()
Dim FSO As Scripting.FileSystemObject
Dim txt As Scripting.TextStream
Dim LogPath As String
Dim LogFile As String
LogPath = "S:\logging"
LogFile = "logfile.txt"

Set FSO = New Scripting.FileSystemObject

' chech file exists
If FSO.DriveExists(Left(LogPath, 1)) Then

If FSO.FolderExists(LogPath) Then


Else

FSO.CreateFolder (LogPath)

End If

Set txt = FSO.OpenTextFile(LogPath & "\" & LogFile, ForAppending,
True)

txt.WriteLine Format(Now, "dd-mmm-yyyy HH:MM")

txt.WriteLine Range("A1").Value
txt.WriteLine Range("A2").Value
txt.WriteLine Range("A3").Value
txt.WriteLine "-----------------"
txt.Close

Set txt = Nothing

Else
' drive doesn't exist - raise an alert
End If
Set FSO = Nothing

End Sub

Patrick Molloy
Microsoft xcel MVP