Copy files from spreadsheet into folder
Hi Everyone,
My question is this. I have a spreadsheet with 350 files (and directory paths) listed, all in column A. What I want to do is this. Copy all of these files into 1 new folder called temp. Does anyone know a quick way to do this? Second problem, Does anyone know a way to create hyperlinks from all 350, without selecting individual and then changing? I've tried a Macro - but it doesn't work because it requires each one to be specifically pointing toward a file, on an individual basis. MANY thanks in advance, |
#1:
Option Explicit Sub testme01() Dim myCell As Range Dim myRng As Range Dim testStr As String With Worksheets("sheet1") Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) For Each myCell In myRng.Cells testStr = "" On Error Resume Next testStr = Dir(myCell.Value) On Error GoTo 0 If testStr = "" Then 'not found myCell.Offset(0, 2).Value = "missing" Else FileCopy Source:=myCell.Value, _ Destination:="C:\temp" End If Next myCell End With End Sub #2: In B1 and dragged down. =hyperlink(a1) Intotao wrote: Hi Everyone, My question is this. I have a spreadsheet with 350 files (and directory paths) listed, all in column A. What I want to do is this. Copy all of these files into 1 new folder called temp. Does anyone know a quick way to do this? Second problem, Does anyone know a way to create hyperlinks from all 350, without selecting individual and then changing? I've tried a Macro - but it doesn't work because it requires each one to be specifically pointing toward a file, on an individual basis. MANY thanks in advance, -- Dave Peterson |
Thanks for your response Dave. Much appreciated.
However after trying the code I keep getting a "Run time error 75 Path/file Access error" I've checked and made sure the file and directory was correct. Were there any other conditions I need to meet for this to work? Just curious, Many thanks again, "Dave Peterson" wrote: #1: Option Explicit Sub testme01() Dim myCell As Range Dim myRng As Range Dim testStr As String With Worksheets("sheet1") Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) For Each myCell In myRng.Cells testStr = "" On Error Resume Next testStr = Dir(myCell.Value) On Error GoTo 0 If testStr = "" Then 'not found myCell.Offset(0, 2).Value = "missing" Else FileCopy Source:=myCell.Value, _ Destination:="C:\temp" End If Next myCell End With End Sub #2: In B1 and dragged down. =hyperlink(a1) Intotao wrote: Hi Everyone, My question is this. I have a spreadsheet with 350 files (and directory paths) listed, all in column A. What I want to do is this. Copy all of these files into 1 new folder called temp. Does anyone know a quick way to do this? Second problem, Does anyone know a way to create hyperlinks from all 350, without selecting individual and then changing? I've tried a Macro - but it doesn't work because it requires each one to be specifically pointing toward a file, on an individual basis. MANY thanks in advance, -- Dave Peterson |
I pasted into C:\temp
Did you modify that to the folder that you wanted to use? If you did, post back with a little more info--what line blew up? Intotao wrote: Thanks for your response Dave. Much appreciated. However after trying the code I keep getting a "Run time error 75 Path/file Access error" I've checked and made sure the file and directory was correct. Were there any other conditions I need to meet for this to work? Just curious, Many thanks again, "Dave Peterson" wrote: #1: Option Explicit Sub testme01() Dim myCell As Range Dim myRng As Range Dim testStr As String With Worksheets("sheet1") Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) For Each myCell In myRng.Cells testStr = "" On Error Resume Next testStr = Dir(myCell.Value) On Error GoTo 0 If testStr = "" Then 'not found myCell.Offset(0, 2).Value = "missing" Else FileCopy Source:=myCell.Value, _ Destination:="C:\temp" End If Next myCell End With End Sub #2: In B1 and dragged down. =hyperlink(a1) Intotao wrote: Hi Everyone, My question is this. I have a spreadsheet with 350 files (and directory paths) listed, all in column A. What I want to do is this. Copy all of these files into 1 new folder called temp. Does anyone know a quick way to do this? Second problem, Does anyone know a way to create hyperlinks from all 350, without selecting individual and then changing? I've tried a Macro - but it doesn't work because it requires each one to be specifically pointing toward a file, on an individual basis. MANY thanks in advance, -- Dave Peterson -- Dave Peterson |
All times are GMT +1. The time now is 09:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com