E-Mail
 

I receive via e-mail 25 spreadsheets every week to consolidate into a group
report. I have constant problems with idiots forgetting to attach the file,
attaching the wrong file, attaching the shortcut. I have been experimenting
with automating the sending of these files to me. However,having read and
tried many of the suggestions on e-mailing with zero success, am I asking too
much to devise code that would work on all these different PC's with
different mailers and very varied versions of windows & office.
--
Roger

E-Mail
 

You could do it very easily.
set up a folder on your network that your users have write access to. In
this folder you may want sub-folders whose names are datestamps...eg 20050315
This is useful as they are easy to sort & check.

Each work book can now have some very simple code to generate a text file
which is saved in the datestamped folder

This code in the user's workbook...its not sophisticated since we're using
various versions of Office and/or Excel...so kept simple:

Sub Submit_Data()
Dim sFileName As String
Dim text As String, vText As Variant
Dim rw As Long
Dim cl As Long

Const FileName As String = "H:\MyData\DATESTAMP\USER_Data.txt"

sFileName = Replace(FileName, "USER", Environ$("username"))
sFileName = Replace(sFileName, "DATESTAMP", Format$(Date, "YYYYMMDD"))

Open sFileName For Output As #1

For rw = 1 To 30
text = ""
For cl = 1 To 5
text = text & "," & Cells(rw, cl)
Next
text = Mid(text, 2)
Print #1, text
Next
Close

End Sub

you'll need to set the rows/columns etc but this tested OK

Your own workbook will need to read in all the data...follows...









"Roger" wrote:

I receive via e-mail 25 spreadsheets every week to consolidate into a group
report. I have constant problems with idiots forgetting to attach the file,
attaching the wrong file, attaching the shortcut. I have been experimenting
with automating the sending of these files to me. However,having read and
tried many of the suggestions on e-mailing with zero success, am I asking too
much to devise code that would work on all these different PC's with
different mailers and very varied versions of windows & office.
--
Roger

E-Mail
 

part 2
This is th esimple code that you'd need to read in data from all the files
in the dated folder. As with the previous code, the assumption is that there
are 5 columns. The import puts the file name in the first column, so you know
"who" sent it

Option Explicit
Sub MergeData()

Const FolderName As String = "H:\MyData\DATESTAMP\"
Dim sFolderName As String

Dim rw As Long
Dim fn As String
Dim ws As Worksheet
Set ws = Worksheets.Add
Dim text As String
sFolderName = Replace(FolderName, "DATESTAMP", Format$(Date, "yyyymmdd"))
fn = Dir(sFolderName)
Do While fn < ""

Open sFolderName & fn For Input As #1
Do Until EOF(1)
text = ""
Line Input #1, text

rw = rw + 1
With ws
.Cells(rw, 1) = fn
.Range(.Cells(rw, 2), .Cells(rw, 6)) = Split(text, ",")
End With
Loop
Close
fn = Dir()

Loop

End Sub

Not the use of good old DIR() ! we could have done something classy with
Scripting FileSystemObject, but this isn't really necessary.

"Patrick Molloy" wrote:

You could do it very easily.
set up a folder on your network that your users have write access to. In
this folder you may want sub-folders whose names are datestamps...eg 20050315
This is useful as they are easy to sort & check.

Each work book can now have some very simple code to generate a text file
which is saved in the datestamped folder

This code in the user's workbook...its not sophisticated since we're using
various versions of Office and/or Excel...so kept simple:

Sub Submit_Data()
Dim sFileName As String
Dim text As String, vText As Variant
Dim rw As Long
Dim cl As Long

Const FileName As String = "H:\MyData\DATESTAMP\USER_Data.txt"

sFileName = Replace(FileName, "USER", Environ$("username"))
sFileName = Replace(sFileName, "DATESTAMP", Format$(Date, "YYYYMMDD"))

Open sFileName For Output As #1

For rw = 1 To 30
text = ""
For cl = 1 To 5
text = text & "," & Cells(rw, cl)
Next
text = Mid(text, 2)
Print #1, text
Next
Close

End Sub

you'll need to set the rows/columns etc but this tested OK

Your own workbook will need to read in all the data...follows...









"Roger" wrote:

I receive via e-mail 25 spreadsheets every week to consolidate into a group
report. I have constant problems with idiots forgetting to attach the file,
attaching the wrong file, attaching the shortcut. I have been experimenting
with automating the sending of these files to me. However,having read and
tried many of the suggestions on e-mailing with zero success, am I asking too
much to devise code that would work on all these different PC's with
different mailers and very varied versions of windows & office.
--
Roger