Copy to different workbook without opening it

[Guest]

I've got the following code that records usage data for userform results.
'Mileage' is the name of the userform. I am merely copying the selection of
a combobox to another workbook. I would like to do this without opening the
other workbook.

Thanks

Sub Mileage_ReportTop5Data()
Dim ClosestTo As Variant
Dim ReportLoc As String

ClosestTo = Mileage.ComboClosestTo.Value
ReportLoc = "\\Trimain2\Public\Agent Forms\Forms\Data Sheets\Mileage
Requests.xls"

Application.ScreenUpdating = False
Workbooks.Open FileName:=ReportLoc
Sheets("Resorts Used").Range("B65536").End(xlUp).Offset(1, 0).Value =
ClosestTo
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

 

[Guest]

Sorry you pretty much have to open it. There is no easy way around it. If I
recall, you can do it with an Excel4 macro but that is really more trouble
than it is worth...

 

[Guest]

Thanks for the reply. Didn't know whether it would work or not.

One other question for you. What is the difference between reading a file
with it still closed and copying to it while closed? I think I remember that
you can pull info from closed workbooks using ADO references. Why the
disparity?

JNW

 

[Guest]

Here is a link to a good refernce for ADO. You can do through ADO but once
again it really is more work than it is worth. Turn Screen updating off. Open
the file. Do your stuff. Close the file and the end user really is none the
wiser. Unless I was trying to do large scale changes which would hold the
file open for long periods of time when other users may want to access it,
then I can not think of a good reason. That being said if that were the case
I would probably use Access where concurrent users is less of an issue.

http://www.erlandsendata.no/english/index.php?t=envbadac

 

[Guest]

Thanks again Jim. It does look like way too much trouble!