Loop through table and download files to local directory

[vanlash]

I have an excel file that takes pasted part numbers and downloads the linked files to a local directory. I'd really like to do this in Access (2007). So the user doesn't have to use two tools to accomplish the task.


I have a table in Access that has the path and file name links to the server location available. I've tried looking for the answer, but am under a deadline to finish.

I would like to loop through the table download all files in the table. Does anyone have the code to do this readily available? Any help would be greatly appreciated!

Signed, totally clueless :)

Here is the excel vba code:

Public sPartNo As String
Public Rev As String
Public iLastRow As Long
Public sDownload As Integer
Public Test2 As String
 
 
Sub Last_Row()
 
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    iLastRow = Selection.Rows.Count
 
End Sub
 
Sub GetDrawing_Link()
Dim iPartNoCol As Integer 'column that contains the part number
Dim iRevCol As Integer 'column that contains the revision
Dim iFirstRow As Long 'first row to process
 
Dim strQuery As String
 
Dim varRowToProcess As Long 'row that is currently being processed
Dim sdir As String
Dim Count, Count2, Count3 As Long
 
Dim varCellTest, varLinkToUse As String
 
sDownload = MsgBox("Do you want to download the files to you computer?", 4, "Download Files to Computer")
 
iPartNoCol = 1
iRevCol = 2
iFirstRow = 2
Count2 = 0
 
Last_Row
 
'Create C:\tempdwgs directory and delete all files
If Dir("c:\tempdwgs", vbDirectory) <> "tempdwgs" Then
    MkDir ("c:\tempdwgs")
End If
 
If Dir("C:\tempdwgs\" & "*.*") = "" = False Then
    Kill "C:\tempdwgs\*.*"
End If
 
For varRowToProcess = iFirstRow To (iLastRow)
 
    sPartNo = Trim(ActiveSheet.Cells(varRowToProcess, iPartNoCol).Value)
 
     Application.ScreenUpdating = False
 
    sSQL = "select locationpath, imgfilename"
    sSQL = sSQL + " from DOC_Image"
    sSQL = sSQL + " WHERE partnumber = '" & sPartNo & "' AND partrev = '" & Rev & "'"
 
    With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN="";Description="";SERVER="";UID=Shareuser;PWD=asdf;DATABASE=""", Destination:=Range("I1"))
        .Sql = sSQL
        .FieldNames = False
        .RefreshStyle = xlOverwriteCells
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .HasAutoFormat = True
        .BackgroundQuery = False
        .TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .SavePassword = False
        .SaveData = True
    End With
    Folder = Range("I1").Value
    Filename = Range("J1").Value
    Link = Folder & "\" & Filename
    Application.DisplayAlerts = False
 
 
    If Link <> "\" Then
        ActiveSheet.Cells(varRowToProcess, iPartNoCol).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
            Link
        If sDownload = 6 Then
            FileCopy Link, "c:\tempdwgs\" & Filename
        End If
    End If
 
Next varRowToProcess
 
If sDownload = 6 Then
Shell "Explorer.exe c:\tempdwgs\"
End If
 
End Sub
 
Sub Get_Rev()
 
    Application.ScreenUpdating = False
 
    sSQL = "select prtrev"
    sSQL = sSQL + " from prtdaily"
    sSQL = sSQL + " where prtno = '" & sPartNo & "'"
 
    With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN="";Description="";SERVER="";UID="";PWD="";DATABASE=""", Destination:=Range("I1"))
        .Sql = sSQL
        .FieldNames = False
        .RefreshStyle = xlOverwriteCells
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .HasAutoFormat = True
        .BackgroundQuery = False
        .TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .SavePassword = False
        .SaveData = True
    End With
    Rev = Range("I1").Value
    Application.DisplayAlerts = False
 
End Sub

[TheSmileyCoder]

When posting over 100 lines of code, its generally a good idea to point out which lines you might want someone to look at.

For copying files, I like to use the filesystemobject. Its quite versatile, and easy to use.

Dim oFSO as FileSystemObject 'Need reference to Windows Script Host Object Model, or use late binding
set oFSO=new FileSystemObject
oFSO.CopyFile sSourcePath,sDestPath
Set oFSO=nothing

[jimatqsi]

vanlash,
You don't make clear whether the files are on your domain or not. If they are you can use the filesystemobject, as TheSMileyCoder suggested. If not, you're probably going to want to use FTP to download those files.

If you know FTP you could use Access to create some text script files with .bat extensions and then execute them from within the VBA. But also there's lot of discussions online about FTP from VBA. Here's one that might get you started quickly:
http://www.access-programmers.co.uk/...d.php?t=178371

Jim

[zmbd]

open a record set on your files
provide the full path for both source and destination
FileCopy Statement
Do ... Until rs.eof

If you need to move the files...
I still prefer the copy first, then verify the file in the new location, then delete the file from the old...

OR

if you just want to move the files in one go:
basically the same as the first
use the Name statement
instead if filecopy.

No need for extra library references nor any fancy codeing

this is old school BASIC (^_^)

-z