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 filesystemobjec t. 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 filesystemobjec t, 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/forums/showthread.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