Drawing from other spreadsheets over a LAN

[Marvin Ettlin]

We have CBT taining, which enters a date in a field once
you completed that training. Because it is on the LAN
and the training officer doesn't want us to have direct
access to his Db... he exports it as a Excel
Spreadsheet. Each week I need to go to the Common site,
save the spreadsheet and import it into my ACCESS Db.
What I would like to do is creat a macro to do this -- it
is very reptative and the name of the spreadsheet never
changes. How d oI go about settin gup this macro -- I
tried one that imports data... but I need the first part
of going to the LAN site and saving it back to my hard
drive... I can set up a specific folder so it will be
always in the same location. When I use run appl... I
can get to the website, but then it tries to find the
table and looks for it in the spot I want to download it
to and since it is not there yet... gives my a stop macro
error. Help....

Marv

 

[Steve Schapel]

Marv,

Why do you need to copy the spreadsheet file to your local disk first?
Any reason you can't use a TransferSpreadsheet macro, and reference
directly to the spreadsheet file on the network?

 

[___]

I use this code to search a folder (here Z:\IMPORTDIRECTORY\)for tex
files ready for import. Once it has imported all files it moves them t
Z:\HISTORYDIRECTORY\

Code
-------------------

Private Sub CmdImport_Click()

Dim InputDir, ImportFile As String, tblName As String, FinalName As String
Dim InputMsg As String
InputDir = "Z:\IMPORTDIRECTORY\"
ImportFile = Dir(InputDir & "\*.txt")
Do While Len(ImportFile) > 0
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1)) 'Use this to import each file into separate tables.
tblName = "YOURTABLENAME" 'I use this to import all my files into one table.
DoCmd.TransferText acImportDelim, "ImpSpec", tblName, InputDir & ImportFile, False
ImportFile = Dir
Loop
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
fs.MoveFile "Z:\IMPORTDIRECTORY\*.txt", "Z:\HISTORYDIRECTORY\"

End Sub

 

[Marv Ettlin]

Steve,
That is what I ended up doing (I kept playing around
with it even after I posted the question)... and then use
the import macro to update the spreadsheet... I was just
wondering if there was an easier way... Thanks for all
the responses though!!!

Marv