Downloading drawings from a list in excel

  • Thread starter Thread starter Gary Tamblyn
  • Start date Start date
G

Gary Tamblyn

Here's a problem, i'm not sure Excel can perform, but if
anyone can, you guys can ;-)

We run a Technical Documentations department, and
currently access a list of engineering drawings from our
servers. All these drawings are in Tiff format, however
the only way to access them, is through a web search, then
a web download. It can be a bit slow.

I would like to be able to put these drawings onto a local
drive to speed things up (there's about 1000 of them)

I know the file path and the file names of all 1000 Tiffs,
in fact I have all the names in a column in an excel file.

Is there a way of excel, I'm guessing in a Macro, moving
down these file addresses and saving the tiff locally ? Or
sending a 'download this file' command to explorer ?

If Excel can't do it, does anyone know of anything else ?

Thanks
Gary
 
Gary Tamblyn said:
Here's a problem, i'm not sure Excel can perform, but if
anyone can, you guys can ;-)

We run a Technical Documentations department, and
currently access a list of engineering drawings from our
servers. All these drawings are in Tiff format, however
the only way to access them, is through a web search, then
a web download. It can be a bit slow.

I would like to be able to put these drawings onto a local
drive to speed things up (there's about 1000 of them)

I know the file path and the file names of all 1000 Tiffs,
in fact I have all the names in a column in an excel file.

Is there a way of excel, I'm guessing in a Macro, moving
down these file addresses and saving the tiff locally ? Or
sending a 'download this file' command to explorer ?

If Excel can't do it, does anyone know of anything else ?

Thanks
Gary
Click to expand...

If you already have the file names in a column
in the excel sheet its pretty trivial

You can add a command button (or menu to a the command bar)
and use the shell function.

Something like this should work if you selected the cell
with the file name

Private Sub CommandButton1_Click()
Dim myfile As String
myfile = "c:\program files\Internet Explorer\iexplore.exe " &
ActiveCell.Value
Shell myfile

End Sub

For information on how to add command bars see

http://www.erlandsendata.no/english/downloads/commandbars.htm

Keith
 
Gary Tamblyn said:
Thank you for your solution, I'm not very expert at Macros
in Excel, so I don't know where i'm going wrong.

I replaced 'sub' with 'private sub' but it failed to pick
up the macro name when I selected 'assign macro' to a
button. I'm obviously doing something wrong here.
Click to expand...

Thats because a Private Sub cant be selected as a macro
Change it to a Public Sub thus

Public Sub ShowMyDrawings
Dim myfile As String
myfile = "c:\program files\Internet
Explorer\iexplore.exe " &
ActiveCell.Value
Shell myfile
End Sub
after changing it to just 'sub' It also came up with
compiler error, Invalid use of property for the line that
reads "ActiveCell.Value" really out of my depth here,
sorry ;-)
Click to expand...

Probably because no cell was selected
you can check for this as shown below

Public Sub ShowMyDrawings()
Dim myfile As String, mydraw As String

mydraw = ActiveCell.Text
On Error GoTo nodraw:

If mydraw = "" Then
GoTo nodraw:
End If

myfile = "c:\program files\Internet Explorer\iexplore.exe " &
ActiveCell.Value
Shell myfile

Exit Sub
nodraw:
MsgBox "please slect a file holding a filename"

End Sub
If I got this to work, would it only do one cell ?
Click to expand...

Nope in any selected cell, just select a cell and select the menu

Would I need to create a loop. It would be good if I could
allocate cell B2 and C2 as parameter cells, providing the
start and finish cells of the loop, so I didn't have to
download the images in one go.

I'm guessing the images would then appear in Explorer's
download folder once they were done ?
Click to expand...

No , all this routine does is view them you could use the
shell function to do a copy though

Suppose you had the network file name in column A and
the copy file name in column B you could do this using
the filecopy function

Public Sub ShowMyDrawings()
Dim myfile As String, mydraw As String
Dim copyfil As String
Dim rownum As Long, colnum As Long

' Get File name
mydraw = ActiveCell.Text
' On Error GoTo nodraw:

' Get Filename for copy
rownum = ActiveCell.Row
colnum = ActiveCell.Column
copyfile = ActiveSheet.Cells(rownum, colnum + 1).Text

If mydraw = "" Then
GoTo nodraw:
End If



' View Drawing
myfile = "c:\program files\Internet Explorer\iexplore.exe " & mydraw
Shell myfile

' Check we picked up a name for the copy
If copyfile="" then
Msgbox "No address found for local copy"
Exit Sub
End If

' Copy drawing
FileCopy mydraw, copyfile
Exit Sub

nodraw:
MsgBox "please select a file holding a filename"

End Sub

Keith
 
Back
Top