By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,191 Members | 1,256 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,191 IT Pros & Developers. It's quick & easy.

Loop through table and download files to local directory

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1. Public sPartNo As String
  2. Public Rev As String
  3. Public iLastRow As Long
  4. Public sDownload As Integer
  5. Public Test2 As String
  6.  
  7.  
  8. Sub Last_Row()
  9.  
  10.     Range("A1").Select
  11.     Range(Selection, Selection.End(xlDown)).Select
  12.     iLastRow = Selection.Rows.Count
  13.  
  14. End Sub
  15.  
  16. Sub GetDrawing_Link()
  17. Dim iPartNoCol As Integer 'column that contains the part number
  18. Dim iRevCol As Integer 'column that contains the revision
  19. Dim iFirstRow As Long 'first row to process
  20.  
  21. Dim strQuery As String
  22.  
  23. Dim varRowToProcess As Long 'row that is currently being processed
  24. Dim sdir As String
  25. Dim Count, Count2, Count3 As Long
  26.  
  27. Dim varCellTest, varLinkToUse As String
  28.  
  29. sDownload = MsgBox("Do you want to download the files to you computer?", 4, "Download Files to Computer")
  30.  
  31. iPartNoCol = 1
  32. iRevCol = 2
  33. iFirstRow = 2
  34. Count2 = 0
  35.  
  36. Last_Row
  37.  
  38. 'Create C:\tempdwgs directory and delete all files
  39. If Dir("c:\tempdwgs", vbDirectory) <> "tempdwgs" Then
  40.     MkDir ("c:\tempdwgs")
  41. End If
  42.  
  43. If Dir("C:\tempdwgs\" & "*.*") = "" = False Then
  44.     Kill "C:\tempdwgs\*.*"
  45. End If
  46.  
  47. For varRowToProcess = iFirstRow To (iLastRow)
  48.  
  49.     sPartNo = Trim(ActiveSheet.Cells(varRowToProcess, iPartNoCol).Value)
  50.  
  51.      Application.ScreenUpdating = False
  52.  
  53.     sSQL = "select locationpath, imgfilename"
  54.     sSQL = sSQL + " from DOC_Image"
  55.     sSQL = sSQL + " WHERE partnumber = '" & sPartNo & "' AND partrev = '" & Rev & "'"
  56.  
  57.     With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN="";Description="";SERVER="";UID=Shareuser;PWD=asdf;DATABASE=""", Destination:=Range("I1"))
  58.         .Sql = sSQL
  59.         .FieldNames = False
  60.         .RefreshStyle = xlOverwriteCells
  61.         .RowNumbers = False
  62.         .FillAdjacentFormulas = False
  63.         .RefreshOnFileOpen = False
  64.         .HasAutoFormat = True
  65.         .BackgroundQuery = False
  66.         .TablesOnlyFromHTML = True
  67.         .Refresh BackgroundQuery:=False
  68.         .SavePassword = False
  69.         .SaveData = True
  70.     End With
  71.     Folder = Range("I1").Value
  72.     Filename = Range("J1").Value
  73.     Link = Folder & "\" & Filename
  74.     Application.DisplayAlerts = False
  75.  
  76.  
  77.     If Link <> "\" Then
  78.         ActiveSheet.Cells(varRowToProcess, iPartNoCol).Select
  79.         ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
  80.             Link
  81.         If sDownload = 6 Then
  82.             FileCopy Link, "c:\tempdwgs\" & Filename
  83.         End If
  84.     End If
  85.  
  86. Next varRowToProcess
  87.  
  88. If sDownload = 6 Then
  89. Shell "Explorer.exe c:\tempdwgs\"
  90. End If
  91.  
  92. End Sub
  93.  
  94. Sub Get_Rev()
  95.  
  96.     Application.ScreenUpdating = False
  97.  
  98.     sSQL = "select prtrev"
  99.     sSQL = sSQL + " from prtdaily"
  100.     sSQL = sSQL + " where prtno = '" & sPartNo & "'"
  101.  
  102.     With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN="";Description="";SERVER="";UID="";PWD="";DATABASE=""", Destination:=Range("I1"))
  103.         .Sql = sSQL
  104.         .FieldNames = False
  105.         .RefreshStyle = xlOverwriteCells
  106.         .RowNumbers = False
  107.         .FillAdjacentFormulas = False
  108.         .RefreshOnFileOpen = False
  109.         .HasAutoFormat = True
  110.         .BackgroundQuery = False
  111.         .TablesOnlyFromHTML = True
  112.         .Refresh BackgroundQuery:=False
  113.         .SavePassword = False
  114.         .SaveData = True
  115.     End With
  116.     Rev = Range("I1").Value
  117.     Application.DisplayAlerts = False
  118.  
  119. End Sub
May 22 '14 #1
Share this Question
Share on Google+
3 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Expand|Select|Wrap|Line Numbers
  1. Dim oFSO as FileSystemObject 'Need reference to Windows Script Host Object Model, or use late binding
  2. set oFSO=new FileSystemObject
  3. oFSO.CopyFile sSourcePath,sDestPath
  4. Set oFSO=nothing
May 22 '14 #2

Expert 100+
P: 1,221
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
May 22 '14 #3

zmbd
Expert Mod 5K+
P: 5,397
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
May 22 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.