|
File not found - error
Hi, Have a macro that opens some files one at a time, and does stuff. If Excel can't find one of the files, it gets i get the VBA file not found error and the code will not continue. I need the code to continue even if it cannot find one of the files, with no errpr messages. I've tried variations of "On Error Resume Next" & "On Error Goto ErrorTrap" with no luck. Sub start get file do work file missing get file do work get file do work end sub Ideas? -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 |
File not found - error
Try a variation of this:
Dim File1 As String Dim FF1 As String File1 = "C:/Temp/H73FJ.xls" FF1 = Dir(File1) If FF1 < "" Then Workbooks.Open Filename:=File1 MsgBox "Do something" Windows(FF1).Close Else MsgBox "File doesn't exist" End If Hope this helps Rowan Piranha wrote: Hi, Have a macro that opens some files one at a time, and does stuff. If Excel can't find one of the files, it gets i get the VBA file not found error and the code will not continue. I need the code to continue even if it cannot find one of the files, with no errpr messages. I've tried variations of "On Error Resume Next" & "On Error Goto ErrorTrap" with no luck. Sub start get file do work file missing get file do work get file do work end sub Ideas? |
File not found - error
Hi,
Yes it is correct that if the file doesnot exists then the code given by Rowan should work good. but how to create the excel file at run time. that is, 1. i want to create a file at execution time. 2. if file already exists then overwrite it. 3. Need to fill up the file with some contents. 4. Later to Save the file with a given name. how this is possible.. thanking you... "Rowan" wrote: Try a variation of this: Dim File1 As String Dim FF1 As String File1 = "C:/Temp/H73FJ.xls" FF1 = Dir(File1) If FF1 < "" Then Workbooks.Open Filename:=File1 MsgBox "Do something" Windows(FF1).Close Else MsgBox "File doesn't exist" End If Hope this helps Rowan Piranha wrote: Hi, Have a macro that opens some files one at a time, and does stuff. If Excel can't find one of the files, it gets i get the VBA file not found error and the code will not continue. I need the code to continue even if it cannot find one of the files, with no errpr messages. I've tried variations of "On Error Resume Next" & "On Error Goto ErrorTrap" with no luck. Sub start get file do work file missing get file do work get file do work end sub Ideas? |
File not found - error
Maybe something like this:
Sub AddFile() Dim newBk As Workbook On Error GoTo ErrorHandler Application.DisplayAlerts = False Set newBk = Workbooks.Add newBk.Sheets(1).Cells(1) = "yourData" newBk.SaveAs ("C:\Temp\Newbk.xls") newBk.Close Set newBk = Nothing ErrorHandler: Application.DisplayAlerts = True End Sub Regards Rowan Yogeshwar wrote: Hi, Yes it is correct that if the file doesnot exists then the code given by Rowan should work good. but how to create the excel file at run time. that is, 1. i want to create a file at execution time. 2. if file already exists then overwrite it. 3. Need to fill up the file with some contents. 4. Later to Save the file with a given name. how this is possible.. thanking you... "Rowan" wrote: Try a variation of this: Dim File1 As String Dim FF1 As String File1 = "C:/Temp/H73FJ.xls" FF1 = Dir(File1) If FF1 < "" Then Workbooks.Open Filename:=File1 MsgBox "Do something" Windows(FF1).Close Else MsgBox "File doesn't exist" End If Hope this helps Rowan Piranha wrote: Hi, Have a macro that opens some files one at a time, and does stuff. If Excel can't find one of the files, it gets i get the VBA file not found error and the code will not continue. I need the code to continue even if it cannot find one of the files, with no errpr messages. I've tried variations of "On Error Resume Next" & "On Error Goto ErrorTrap" with no luck. Sub start get file do work file missing get file do work get file do work end sub Ideas? |
File not found - error
Hi rowan, I am working on this. The way i understand is, i have to put your code on every file i am opening. Is that correct? If i am correct is there a way to, resume the next task, for the whole macro, instead of each task seperatly? Thx for your input. Dave Rowan Wrote: Try a variation of this: Dim File1 As String Dim FF1 As String File1 = "C:/Temp/H73FJ.xls" FF1 = Dir(File1) If FF1 < "" Then Workbooks.Open Filename:=File1 MsgBox "Do something" Windows(FF1).Close Else MsgBox "File doesn't exist" End If Hope this helps Rowan Piranha wrote: Hi, Have a macro that opens some files one at a time, and does stuff. If Excel can't find one of the files, it gets i get the VBA file not found error and the code will not continue. I need the code to continue even if it cannot find one of the files, with no errpr messages. I've tried variations of "On Error Resume Next" & "On Error Goto ErrorTrap" with no luck. Sub start get file do work file missing get file do work get file do work end sub Ideas? -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 |
File not found - error
Hi Dave
You would need to have this for each book you are opening. If you are going to perform the same tasks on each book opened you could have these in a seperate macro which you call from the main routine eg: Sub GetFiles() Dim File1 As String Dim FF1 As String File1 = "C:/Temp/FirstFile.xls" FF1 = Dir(File1) If FF1 < "" Then Call DoStuff(File1) FF1 = "" End If File1 = "C:/Temp/AnotherFile.xls" FF1 = Dir(File1) If FF1 < "" Then Call DoStuff(File1) FF1 = "" End If 'etc End Sub Sub DoStuff(File1 As String) Workbooks.Open Filename:=File1 'Perform other tasks on file 'save and close if required End Sub Note the main macro "GetFiles" passes the variable File1 to "DoStuff" which is then used to open the appropriate file. This bit I am afraid I did not understand: If i am correct is there a way to, resume the next task, for the whole macro, instead of each task seperatly? The code above will check each file and perform whatever tasks you put in DoStuff on each file found. Hope this helps Rowan Piranha wrote: Hi rowan, I am working on this. The way i understand is, i have to put your code on every file i am opening. Is that correct? If i am correct is there a way to, resume the next task, for the whole macro, instead of each task seperatly? Thx for your input. Dave |
File not found - error
Hi Dave,
If the processing steps are the same for each workbook opened, then perhaps try something like: Sub Tester03A() Dim arr As Variant Dim WB As Workbook Dim i As Long 'Workbooks to open arr = Array("C:\Book1.xls", "C:\BookB.xls", _ "C:\Book100.xls", "C:\Book200.xls") 'Open, process and close each workbook sequentially For i = LBound(arr) To UBound(arr) Set WB = Nothing On Error Resume Next Set WB = Workbooks.Open(arr(i)) On Error GoTo 0 If Not WB Is Nothing Then 'Do something, e.g.: MsgBox WB.Name WB.Close SaveChanges:=True Else 'Workbook not found 'Do something else, e.g.: MsgBox arr(i) & " not found!" End If Next i End Sub '================== --- Regards, Norman "Piranha" wrote in message ... Hi rowan, I am working on this. The way i understand is, i have to put your code on every file i am opening. Is that correct? If i am correct is there a way to, resume the next task, for the whole macro, instead of each task seperatly? Thx for your input. Dave Rowan Wrote: Try a variation of this: Dim File1 As String Dim FF1 As String File1 = "C:/Temp/H73FJ.xls" FF1 = Dir(File1) If FF1 < "" Then Workbooks.Open Filename:=File1 MsgBox "Do something" Windows(FF1).Close Else MsgBox "File doesn't exist" End If Hope this helps Rowan Piranha wrote: Hi, Have a macro that opens some files one at a time, and does stuff. If Excel can't find one of the files, it gets i get the VBA file not found error and the code will not continue. I need the code to continue even if it cannot find one of the files, with no errpr messages. I've tried variations of "On Error Resume Next" & "On Error Goto ErrorTrap" with no luck. Sub start get file do work file missing get file do work get file do work end sub Ideas? -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 |
File not found - error
Rowan, This sounds good. I am trying to make this work in a workbook. If you are going to perform the same tasks on each book opened you coul have these in a seperate macro which you call from the main routine eg: Hi Norman, Yours sounds good as well. I am also trying to make it work. One thin i don't understand how to do is, where you have the workbooks to open "hard coded". I'm calling my workbooks from a name, on a list, on a hidden workshee as they change occasionally. Also the path to the workbook is variable as that wil change occasionally. Norman Jones Wrote: Hi Dave, 'Workbooks to open arr = Array("C:\Book1.xls", "C:\BookB.xls", _ "C:\Book100.xls", "C:\Book200.xls") 'Open, process and close each workbook sequentially For i = LBound(arr) To UBound(arr) Set WB = Nothing On Error Resume Next Set WB = Workbooks.Open(arr(i)) On Error GoTo 0 --- Regards, Norman "Piranha" wrot in message ... Hi rowan, I am working on this. The way i understand is, i have to put you code on every file i am opening. Is that correct? If i am correct is there a way to, resume the next task, for th whole macro, instead of each task seperatly? Thx for your input. Dave Rowan Wrote: Try a variation of this: Dim File1 As String Dim FF1 As String File1 = "C:/Temp/H73FJ.xls" FF1 = Dir(File1) If FF1 < "" Then Workbooks.Open Filename:=File1 MsgBox "Do something" Windows(FF1).Close Else MsgBox "File doesn't exist" End If Hope this helps Rowan Piranha wrote: Hi, Have a macro that opens some files one at a time, and does stuff. If Excel can't find one of the files, it gets i get the VBA fil not found error and the code will not continue. I need the code to continue even if it cannot find one of th files, with no errpr messages. I've tried variations of "On Error Resume Next" & "On Error Goto ErrorTrap" with no luck. Sub start get file do work file missing get file do work get file do work end sub Ideas? -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread http://www.excelforum.com/showthread...hreadid=467024 -- Piranh ----------------------------------------------------------------------- Piranha's Profile: http://www.excelforum.com/member.php...fo&userid=2043 View this thread: http://www.excelforum.com/showthread.php?threadid=46702 |
File not found - error
Hi Dave,
Assume that the file names (including the path) list starts in A1 on the hidden sheet. Try: '===================== Sub Tester03B() Dim arr As Variant Dim WB As Workbook Dim rng As Range Dim rCell As Range Dim i As Long Set rng = ThisWorkbook.Sheets("MyHiddenSheet"). _ Range("A1").CurrentRegion.Columns(1) '<<==== CHANGE 'Open, process and close each workbook sequentially For Each rCell In rng.Cells Set WB = Nothing On Error Resume Next Set WB = Workbooks.Open(rCell.Value) On Error GoTo 0 If Not WB Is Nothing Then 'Do something, e.g.: MsgBox WB.Name WB.Close SaveChanges:=True Else 'Workbook not found 'Do something else, e.g.: MsgBox rCell.Value & " not found!" End If Next rCell End Sub '================== Change "MyHiddenSheet" to accord with the name of your hidden sheet. --- Regards, Norman "Piranha" wrote in message ... Rowan, This sounds good. I am trying to make this work in a workbook. If you are going to perform the same tasks on each book opened you could have these in a seperate macro which you call from the main routine eg: Hi Norman, Yours sounds good as well. I am also trying to make it work. One thing i don't understand how to do is, where you have the workbooks to open "hard coded". I'm calling my workbooks from a name, on a list, on a hidden worksheet as they change occasionally. Also the path to the workbook is variable as that will change occasionally. Norman Jones Wrote: Hi Dave, 'Workbooks to open arr = Array("C:\Book1.xls", "C:\BookB.xls", _ "C:\Book100.xls", "C:\Book200.xls") 'Open, process and close each workbook sequentially For i = LBound(arr) To UBound(arr) Set WB = Nothing On Error Resume Next Set WB = Workbooks.Open(arr(i)) On Error GoTo 0 --- Regards, Norman "Piranha" wrote in message ... Hi rowan, I am working on this. The way i understand is, i have to put your code on every file i am opening. Is that correct? If i am correct is there a way to, resume the next task, for the whole macro, instead of each task seperatly? Thx for your input. Dave Rowan Wrote: Try a variation of this: Dim File1 As String Dim FF1 As String File1 = "C:/Temp/H73FJ.xls" FF1 = Dir(File1) If FF1 < "" Then Workbooks.Open Filename:=File1 MsgBox "Do something" Windows(FF1).Close Else MsgBox "File doesn't exist" End If Hope this helps Rowan Piranha wrote: Hi, Have a macro that opens some files one at a time, and does stuff. If Excel can't find one of the files, it gets i get the VBA file not found error and the code will not continue. I need the code to continue even if it cannot find one of the files, with no errpr messages. I've tried variations of "On Error Resume Next" & "On Error Goto ErrorTrap" with no luck. Sub start get file do work file missing get file do work get file do work end sub Ideas? -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 |
File not found - error
Hi Norman, I stuck in my "calling the workbook" code, below, in red, There are 1 source workbooks and it copies the bottom lines to my master workbook. I don't have a hard coded path cause it will change. Thx Dave Norman Jones Wrote: Hi Dave, Assume that the file names (including the path) list starts in A1 o the hidden sheet. Try: '===================== Sub Tester03B() Dim arr As Variant Dim WB As Workbook Dim rng As Range Dim rCell As Range Dim i As Long Set rng = ThisWorkbook.Sheets("MyHiddenSheet"). _ Range("A1").CurrentRegion.Columns(1) '<<==== CHANGE '''''''''''''''''''''''''''''''''' Dim rngFileNames As Range, rngfilename As Range, wb As Workbook With Worksheets("sheet2") Set rngFileNames = .Range("B1") For Each rngfilename In rngFileNames ''''Open file listed in B1. This is just the file name in B1. Set wb = Workbooks.Open(ThisWorkbook.Path & "\" rngfilename)'''''''''''''''''''''''''''''''''' 'Open, process and close each workbook sequentially For Each rCell In rng.Cells Set WB = Nothing On Error Resume Next Set WB = Workbooks.Open(rCell.Value) On Error GoTo 0 '''''''''''''''''''''''''''' So i have to copy the below code 19 times and stick a worksheet code in each one right? ''''''''''''''''''''''''''''''' If Not WB Is Nothing Then 'Do something, e.g.: MsgBox WB.Name WB.Close SaveChanges:=True Else 'Workbook not found 'Do something else, e.g.: MsgBox rCell.Value & " not found!" End If Next rCell End Sub '================== Change "MyHiddenSheet" to accord with the name of your hidden sheet. --- Regards, Norman "Piranha" wrot in message ... Rowan, This sounds good. I am trying to make this work in a workbook. If you are going to perform the same tasks on each book opened yo could have these in a seperate macro which you call from the main routine eg: Hi Norman, Yours sounds good as well. I am also trying to make it work. On thing i don't understand how to do is, where you have the workbooks to open "hard coded". I'm calling my workbooks from a name, on a list, on a hidde worksheet as they change occasionally. Also the path to the workbook is variable as that will change occasionally. Norman Jones Wrote: Hi Dave, 'Workbooks to open arr = Array("C:\Book1.xls", "C:\BookB.xls", _ "C:\Book100.xls", "C:\Book200.xls") 'Open, process and close each workbook sequentially For i = LBound(arr) To UBound(arr) Set WB = Nothing On Error Resume Next Set WB = Workbooks.Open(arr(i)) On Error GoTo 0 --- Regards, Norman "Piranha" wrote in messag ... Hi rowan, I am working on this. The way i understand is, i have to put your code on every file i am opening. Is that correct? If i am correct is there a way to, resume the next task, for the whole macro, instead of each task seperatly? Thx for your input. Dave Rowan Wrote: Try a variation of this: Dim File1 As String Dim FF1 As String File1 = "C:/Temp/H73FJ.xls" FF1 = Dir(File1) If FF1 < "" Then Workbooks.Open Filename:=File1 MsgBox "Do something" Windows(FF1).Close Else MsgBox "File doesn't exist" End If Hope this helps Rowan Piranha wrote: Hi, Have a macro that opens some files one at a time, and doe stuff. If Excel can't find one of the files, it gets i get the VBA file not found error and the code will not continue. I need the code to continue even if it cannot find one of the files, with no errpr messages. I've tried variations of "On Error Resume Next" & "On Error Goto ErrorTrap" with no luck. Sub start get file do work file missing get file do work get file do work end sub Ideas? -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 |
File not found - error
Hi Rowan & Norman, When i look at your codes here on the screen, i can almost make sense of them. However i have spent many hours trying to get them to work with my spread sheet, to no avail. You guys have any other tricks up your sleeves? Just to resummarize. 1- Sheet2 has the filenames B1:B19 (IE: thisfile.xls) 2- The files are called from the filenames on sheet2 as that is inputed by user. The names will change by user, so this must be variable.. 3- 19 workbooks are the source of the data 4- They are in the same folder, (which will change name, so this must be variable) 5- The last used row of the 19 worksheeets is copied to specific rows in the master workbook NOTE: _ALL_this_works_great._ I just need to fix it so if a line on sheet2 OR a file is missing from the folder. the code will continue to run and gather all other data. The way it is now in either of those happens, a popup for FILE MISSING displays and code stops. Thank you, both of you guys, for your input you have contributed so far, Sorry im so dense, on this. Dave -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 |
File not found - error
Hi Dave,
Try this minor modification: '===================== Sub Tester03C() Dim arr As Variant Dim WB As Workbook Dim rng As Range Dim rCell As Range Dim i As Long 'Change range and / or sheet details to suit Set rng = ThisWorkbook.Sheets("Sheet2").Range("B1:B9") 'Open, process and close each workbook sequentially For Each rCell In rng.Cells Set WB = Nothing On Error Resume Next Set WB = Workbooks.Open(rCell.Value) On Error GoTo 0 If Not WB Is Nothing Then 'Your Copy Code WB.Close SaveChanges:=False Else 'Workbook not found - Do nothing! End If Next rCell End Sub '================== --- Regards, Norman "Piranha" wrote in message ... Hi Rowan & Norman, When i look at your codes here on the screen, i can almost make sense of them. However i have spent many hours trying to get them to work with my spread sheet, to no avail. You guys have any other tricks up your sleeves? Just to resummarize. 1- Sheet2 has the filenames B1:B19 (IE: thisfile.xls) 2- The files are called from the filenames on sheet2 as that is inputed by user. The names will change by user, so this must be variable.. 3- 19 workbooks are the source of the data 4- They are in the same folder, (which will change name, so this must be variable) 5- The last used row of the 19 worksheeets is copied to specific rows in the master workbook NOTE: _ALL_this_works_great._ I just need to fix it so if a line on sheet2 OR a file is missing from the folder. the code will continue to run and gather all other data. The way it is now in either of those happens, a popup for FILE MISSING displays and code stops. Thank you, both of you guys, for your input you have contributed so far, Sorry im so dense, on this. Dave -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 |
File not found - error
Hi Norman, I just don't understand. See below in red. If i take the "set" part out and put it on top that don't work. i've tried changing the rngFileName stuff to rcell, i'v tried changing the ranges, taking out the range stuff for sheet2 in my code, and a bunch more. I have 19 of the codes like the red one below, one after the other. Norman Jones Wrote: Hi Dave, Try this minor modification: '===================== Sub Tester03C() Dim arr As Variant Dim WB As Workbook Dim rng As Range Dim rCell As Range Dim i As Long 'Change range and / or sheet details to suit Set rng = ThisWorkbook.Sheets("Sheet2").Range("B1:B9") 'Open, process and close each workbook sequentially For Each rCell In rng.Cells Set WB = Nothing On Error Resume Next Set WB = Workbooks.Open(rCell.Value) On Error GoTo 0 If Not WB Is Nothing Then 'Your Copy Code ''########## 'This is copying workbook # 1 Dim rngFileNames As Range, rngfilename As Range, wb As Workbook 'Selecting filename list of filenames on sheet 2. With Worksheets("sheet2") Set rngFileNames = .Range("B1") 'The Range above advances one row for each workbook name "IE the next block of code will say ("B2") For Each rngfilename In rngFileNames 'Open file listed in B1. Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & rngfilename) 'Copy data from file LastRow = Range("C65536").End(xlUp).Row Range("A" & LastRow).EntireRow.Copy 'Select file, range, to paste to. Windows("WeeklyNumbers_19_ForMM.xls").Activate Range("A6").Select 'The range above advances two lines for each workbook. 'IE workbook # 2 will be ("A8") etc ActiveSheet.Paste 'Finalize paste. Application.CutCopyMode = False Range("F1").Select 'Close file copied from. wb.Close Next End With ''########## WB.Close SaveChanges:=False Else 'Workbook not found - Do nothing! End If Next rCell End Sub '================== --- Regards, Norman ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 [/color] -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 |
File not found - error
Hi Dave,
I just don't understand. See below in red. Reading plain text NG posts, I (and most contributors to the NG) am unable to see your 'red' data. In your previous post you said: NOTE: _ALL_this_works_great._ I just need to fix it so if a line on sheet2 OR a file is missing from the folder. the code will continue to run and gather all other data. The way it is now in either of those happens, a popup for FILE MISSING displays and code stops If you wish, send me a copy of the master file with the 'ALL_this_works_great code' and a typical example of one of the 19 subsidiary workbooks. By all means change // remove any sensitive data. --- Regards, Norman "Piranha" wrote in message ... Hi Norman, I just don't understand. See below in red. If i take the "set" part out and put it on top that don't work. i've tried changing the rngFileName stuff to rcell, i'v tried changing the ranges, taking out the range stuff for sheet2 in my code, and a bunch more. I have 19 of the codes like the red one below, one after the other. Norman Jones Wrote: Hi Dave, Try this minor modification: '===================== Sub Tester03C() Dim arr As Variant Dim WB As Workbook Dim rng As Range Dim rCell As Range Dim i As Long 'Change range and / or sheet details to suit Set rng = ThisWorkbook.Sheets("Sheet2").Range("B1:B9") 'Open, process and close each workbook sequentially For Each rCell In rng.Cells Set WB = Nothing On Error Resume Next Set WB = Workbooks.Open(rCell.Value) On Error GoTo 0 If Not WB Is Nothing Then 'Your Copy Code ''########## 'This is copying workbook # 1 Dim rngFileNames As Range, rngfilename As Range, wb As Workbook 'Selecting filename list of filenames on sheet 2. With Worksheets("sheet2") Set rngFileNames = .Range("B1") 'The Range above advances one row for each workbook name "IE the next block of code will say ("B2") For Each rngfilename In rngFileNames 'Open file listed in B1. Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & rngfilename) 'Copy data from file LastRow = Range("C65536").End(xlUp).Row Range("A" & LastRow).EntireRow.Copy 'Select file, range, to paste to. Windows("WeeklyNumbers_19_ForMM.xls").Activate Range("A6").Select 'The range above advances two lines for each workbook. 'IE workbook # 2 will be ("A8") etc ActiveSheet.Paste 'Finalize paste. Application.CutCopyMode = False Range("F1").Select 'Close file copied from. wb.Close Next End With ''########## WB.Close SaveChanges:=False Else 'Workbook not found - Do nothing! End If Next rCell End Sub '================== --- Regards, Norman ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 [/color] |
File not found - error
Norman, Whats the url where you can do this? DaveNorman Jones Wrote: Hi Dave, Reading plain text NG posts, I (and most contributors to the NG) a unable to see your 'red' data. Regards, Norman "Piranha" wrot in message .. [/url -- Piranh ----------------------------------------------------------------------- Piranha's Profile: http://www.excelforum.com/member.php...fo&userid=2043 View this thread: http://www.excelforum.com/showthread.php?threadid=46702 |
File not found - error
Hi Dave,
See the following comprehensive post from Dave Peterson: http://tinyurl.com/b6oyc --- Regards, Norman "Piranha" wrote in message ... Norman, Whats the url where you can do this? DaveNorman Jones Wrote: Hi Dave, Reading plain text NG posts, I (and most contributors to the NG) am unable to see your 'red' data. Regards, Norman "Piranha" wrote in message .. [/url] -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 |
File not found - error
Norman Jones Wrote: Hi Dave, See the following comprehensive post from Dave Peterson: http://tinyurl.com/b6oyc --- Regards, Norman "Piranha" wrot in message ... Norman, Whats the url where you can do this? DaveNorman Jones Wrote: Hi Dave, Reading plain text NG posts, I (and most contributors to the NG) am unable to see your 'red' data. Regards, Norman "Piranha" wrote in message .. [/url] -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread http://www.excelforum.com/showthread...hreadid=467024 Hi Norman, Thanks for the link, very interesting. For anyone interested in this thread. Norman has furnished me with th following code, which solves all my problems. It works flawlessly. Thank you very very much Norman. Code ------------------- ‘=================== Sub CopyPasteStoreData() Dim rngFileNames As Range Dim rCell As Range Dim WB As Workbook Dim filelistSH As Worksheet Dim copySH As Worksheet Dim destSH As Worksheet Dim RngCopy As Range Dim RngDest As Range Dim LastRow As Long Dim iCtr As Long ActiveSheet.Unprotect password:="xxx" 'Initially, delete old data!! ThisWorkbook.Sheets(1).Range("List").ClearContents Application.DisplayAlerts = False Application.ScreenUpdating = False With ThisWorkbook Set filelistSH = .Sheets("Sheet2") Set destSH = .Sheets("sheet1") End With With filelistSH LastRow = .Cells(Rows.Count, "B").End(xlUp).Row Set rngFileNames = .Range("B1").Resize(LastRow) End With For Each rCell In rngFileNames.Cells If Not IsEmpty(rCell) Then 'Open file listed in B1. On Error Resume Next 'In case file not found! Set WB = Nothing Set WB = Workbooks.Open(ThisWorkbook.Path & "\" & rCell.Value) On Error GoTo 0 If Not WB Is Nothing Then Set copySH = WB.Sheets(1) Set RngCopy = copySH.Cells(Rows.Count, "C").End(xlUp).EntireRow Set RngDest = destSH.Range("A6").Offset(iCtr) RngCopy.Copy Destination:=RngDest 'Close file copied from. WB.Close savechanges:=False iCtr = iCtr + 2 End If End If Next rCell destSH.Range("F1").Select Application.ScreenUpdating = True Application.DisplayAlerts = True ActiveSheet.Protect , password:="xxx" End Sub ‘<<==================== ------------------- -- Piranh ----------------------------------------------------------------------- Piranha's Profile: http://www.excelforum.com/member.php...fo&userid=2043 View this thread: http://www.excelforum.com/showthread.php?threadid=46702 |
File not found - error
Hi Dave,
As a minor addendum, should you wish to hide the deletion of historic data from the user, move the line ( and comment): 'Initially, delete old data!! ThisWorkbook.Sheets(1).Range("List").ClearContents down two lines, so that it follows the instruction: Application.ScreenUpdating = False --- Regards, Norman "Piranha" wrote in message ... Norman Jones Wrote: Hi Dave, See the following comprehensive post from Dave Peterson: http://tinyurl.com/b6oyc --- Regards, Norman "Piranha" wrote in message ... Norman, Whats the url where you can do this? DaveNorman Jones Wrote: Hi Dave, Reading plain text NG posts, I (and most contributors to the NG) am unable to see your 'red' data. Regards, Norman "Piranha" wrote in message .. [/url] -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 Hi Norman, Thanks for the link, very interesting. For anyone interested in this thread. Norman has furnished me with the following code, which solves all my problems. It works flawlessly. Thank you very very much Norman. Code: -------------------- '=================== Sub CopyPasteStoreData() Dim rngFileNames As Range Dim rCell As Range Dim WB As Workbook Dim filelistSH As Worksheet Dim copySH As Worksheet Dim destSH As Worksheet Dim RngCopy As Range Dim RngDest As Range Dim LastRow As Long Dim iCtr As Long ActiveSheet.Unprotect password:="xxx" 'Initially, delete old data!! ThisWorkbook.Sheets(1).Range("List").ClearContents Application.DisplayAlerts = False Application.ScreenUpdating = False With ThisWorkbook Set filelistSH = .Sheets("Sheet2") Set destSH = .Sheets("sheet1") End With With filelistSH LastRow = .Cells(Rows.Count, "B").End(xlUp).Row Set rngFileNames = .Range("B1").Resize(LastRow) End With For Each rCell In rngFileNames.Cells If Not IsEmpty(rCell) Then 'Open file listed in B1. On Error Resume Next 'In case file not found! Set WB = Nothing Set WB = Workbooks.Open(ThisWorkbook.Path & "\" & rCell.Value) On Error GoTo 0 If Not WB Is Nothing Then Set copySH = WB.Sheets(1) Set RngCopy = copySH.Cells(Rows.Count, "C").End(xlUp).EntireRow Set RngDest = destSH.Range("A6").Offset(iCtr) RngCopy.Copy Destination:=RngDest 'Close file copied from. WB.Close savechanges:=False iCtr = iCtr + 2 End If End If Next rCell destSH.Range("F1").Select Application.ScreenUpdating = True Application.DisplayAlerts = True ActiveSheet.Protect , password:="xxx" End Sub '<<===================== -------------------- -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 |
File not found - error
Norman, Yes that works GREAT. Thanks very much. Dave Norman Jones Wrote: Hi Dave, As a minor addendum, should you wish to hide the deletion of histori data from the user, move the line ( and comment): 'Initially, delete old data!! ThisWorkbook.Sheets(1).Range("List").ClearContents down two lines, so that it follows the instruction: Application.ScreenUpdating = False --- Regards, Norman "Piranha" wrot in message ... Norman Jones Wrote: Hi Dave, See the following comprehensive post from Dave Peterson: http://tinyurl.com/b6oyc --- Regards, Norman "Piranha" wrote in messag ... Norman, Whats the url where you can do this? DaveNorman Jones Wrote: Hi Dave, Reading plain text NG posts, I (and most contributors to the NG am unable to see your 'red' data. Regards, Norman "Piranha" wrote in messag .. [/url] -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 Hi Norman, Thanks for the link, very interesting. For anyone interested in this thread. Norman has furnished me wit the following code, which solves all my problems. It works flawlessly. Thank you very very much Norman. Code: -------------------- '=================== Sub CopyPasteStoreData() Dim rngFileNames As Range Dim rCell As Range Dim WB As Workbook Dim filelistSH As Worksheet Dim copySH As Worksheet Dim destSH As Worksheet Dim RngCopy As Range Dim RngDest As Range Dim LastRow As Long Dim iCtr As Long ActiveSheet.Unprotect password:="xxx" 'Initially, delete old data!! ThisWorkbook.Sheets(1).Range("List").ClearContents Application.DisplayAlerts = False Application.ScreenUpdating = False With ThisWorkbook Set filelistSH = .Sheets("Sheet2") Set destSH = .Sheets("sheet1") End With With filelistSH LastRow = .Cells(Rows.Count, "B").End(xlUp).Row Set rngFileNames = .Range("B1").Resize(LastRow) End With For Each rCell In rngFileNames.Cells If Not IsEmpty(rCell) Then 'Open file listed in B1. On Error Resume Next 'In case file not found! Set WB = Nothing Set WB = Workbooks.Open(ThisWorkbook.Path & "\" & rCell.Value) On Error GoTo 0 If Not WB Is Nothing Then Set copySH = WB.Sheets(1) Set RngCopy = copySH.Cells(Rows.Count, "C").End(xlUp).EntireRow Set RngDest = destSH.Range("A6").Offset(iCtr) RngCopy.Copy Destination:=RngDest 'Close file copied from. WB.Close savechanges:=False iCtr = iCtr + 2 End If End If Next rCell destSH.Range("F1").Select Application.ScreenUpdating = True Application.DisplayAlerts = True ActiveSheet.Protect , password:="xxx" End Sub '<<===================== -------------------- -- Piranha ------------------------------------------------------------------------ Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread http://www.excelforum.com/showthread...hreadid=467024 -- Piranh ----------------------------------------------------------------------- Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435 View this thread: http://www.excelforum.com/showthread...hreadid=467024 |
| All times are GMT +1. The time now is 11:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com