Downloading drawings from a list in excel
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 |
Downloading drawings from a list in excel
"Gary Tamblyn" wrote in message ... 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 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/...ommandbars.htm Keith |
Downloading drawings from a list in excel
"Gary Tamblyn" wrote in message ... 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. 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 ;-) 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 ? 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 ? 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 |
All times are GMT +1. The time now is 04:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com