| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need some more eyes to look at this ...
I'm not sure how to 'bump' my previous thread to the first page again
(so more people will see it), so please take a look at this post: http://groups.google.com/group/micro...a6ea267ceb09a4 thanks, ray |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need some more eyes to look at this ...
Hi Ray
Maybe there is a problem with the file ? Try one of the code samples on this page http://www.rondebruin.nl/copy3.htm Or try the add-in (link on the page above) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ray" wrote in message ups.com... I'm not sure how to 'bump' my previous thread to the first page again (so more people will see it), so please take a look at this post: http://groups.google.com/group/micro...a6ea267ceb09a4 thanks, ray |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need some more eyes to look at this ...
On May 7, 11:10 am, "Ron de Bruin" wrote:
Hi Ray Maybe there is a problem with the file ? Try one of the code samples on this pagehttp://www.rondebruin.nl/copy3.htm Or try the add-in (link on the page above) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ray" wrote in oglegroups.com... I'm not sure how to 'bump' my previous thread to the first page again (so more people will see it), so please take a look at this post: http://groups.google.com/group/micro...rogramming/bro... thanks, ray Hi Ron, thanks for your response ... I just ran a 'test' of sorts and I think I've narrowed the problem down, but still don't know how to fix it ... The test was this: I took out all of the source files that have worked properly before, leaving approx 20 files (incl the file that the code had continually stopped on). This time, the 'problem' file worked ok, but the code STILL errored out on the same line (destrange.Value = sourceRange.Value) on the 16th file. So, it would seem that I'm defininely over-running some type of buffer, perhaps the clipboard? I've tried this code: Application.CutCopyMode = False at the end of each loop, to clear the clipboard .... as well as this code: Public Declare Function OpenClipboard Lib "user32" ( _ ByVal hwnd AsLong) As Long Public Declare Function CloseClipboard Lib "user32" () As Long Public Declare Function EmptyClipboard Lib "user32" () As Long Sub ClearClipboard() OpenClipboard (0&) EmptyClipboard CloseClipboard End Sub (via Frank Kabel) No luck on either one ... any more thoughts? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need some more eyes to look at this ...
I not understand the code that you use now
You loop through the files and copy the columns(27) to the same place each time ? Is that correct ? Tell me what you want then I can make another example for you that you can try -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ray" wrote in message oups.com... On May 7, 11:10 am, "Ron de Bruin" wrote: Hi Ray Maybe there is a problem with the file ? Try one of the code samples on this pagehttp://www.rondebruin.nl/copy3.htm Or try the add-in (link on the page above) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ray" wrote in oglegroups.com... I'm not sure how to 'bump' my previous thread to the first page again (so more people will see it), so please take a look at this post: http://groups.google.com/group/micro...rogramming/bro... thanks, ray Hi Ron, thanks for your response ... I just ran a 'test' of sorts and I think I've narrowed the problem down, but still don't know how to fix it ... The test was this: I took out all of the source files that have worked properly before, leaving approx 20 files (incl the file that the code had continually stopped on). This time, the 'problem' file worked ok, but the code STILL errored out on the same line (destrange.Value = sourceRange.Value) on the 16th file. So, it would seem that I'm defininely over-running some type of buffer, perhaps the clipboard? I've tried this code: Application.CutCopyMode = False at the end of each loop, to clear the clipboard .... as well as this code: Public Declare Function OpenClipboard Lib "user32" ( _ ByVal hwnd AsLong) As Long Public Declare Function CloseClipboard Lib "user32" () As Long Public Declare Function EmptyClipboard Lib "user32" () As Long Sub ClearClipboard() OpenClipboard (0&) EmptyClipboard CloseClipboard End Sub (via Frank Kabel) No luck on either one ... any more thoughts? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need some more eyes to look at this ...
You can also try it with PasteSpecial Values and see if you have the same problem
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... I not understand the code that you use now You loop through the files and copy the columns(27) to the same place each time ? Is that correct ? Tell me what you want then I can make another example for you that you can try -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ray" wrote in message oups.com... On May 7, 11:10 am, "Ron de Bruin" wrote: Hi Ray Maybe there is a problem with the file ? Try one of the code samples on this pagehttp://www.rondebruin.nl/copy3.htm Or try the add-in (link on the page above) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ray" wrote in oglegroups.com... I'm not sure how to 'bump' my previous thread to the first page again (so more people will see it), so please take a look at this post: http://groups.google.com/group/micro...rogramming/bro... thanks, ray Hi Ron, thanks for your response ... I just ran a 'test' of sorts and I think I've narrowed the problem down, but still don't know how to fix it ... The test was this: I took out all of the source files that have worked properly before, leaving approx 20 files (incl the file that the code had continually stopped on). This time, the 'problem' file worked ok, but the code STILL errored out on the same line (destrange.Value = sourceRange.Value) on the 16th file. So, it would seem that I'm defininely over-running some type of buffer, perhaps the clipboard? I've tried this code: Application.CutCopyMode = False at the end of each loop, to clear the clipboard .... as well as this code: Public Declare Function OpenClipboard Lib "user32" ( _ ByVal hwnd AsLong) As Long Public Declare Function CloseClipboard Lib "user32" () As Long Public Declare Function EmptyClipboard Lib "user32" () As Long Sub ClearClipboard() OpenClipboard (0&) EmptyClipboard CloseClipboard End Sub (via Frank Kabel) No luck on either one ... any more thoughts? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need some more eyes to look at this ...
On May 7, 1:12 pm, "Ron de Bruin" wrote:
You can also try it with PasteSpecial Values and see if you have the same problem -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in .. . I not understand the code that you use now You loop through the files and copy the columns(27) to the same place each time ? Is that correct ? Tell me what you want then I can make another example for you that you can try -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ray" wrote in ooglegroups.com... On May 7, 11:10 am, "Ron de Bruin" wrote: Hi Ray Maybe there is a problem with the file ? Try one of the code samples on this pagehttp://www.rondebruin.nl/copy3.htm Or try the add-in (link on the page above) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ray" wrote in oglegroups.com... I'm not sure how to 'bump' my previous thread to the first page again (so more people will see it), so please take a look at this post: http://groups.google.com/group/micro...rogramming/bro... thanks, ray Hi Ron, thanks for your response ... I just ran a 'test' of sorts and I think I've narrowed the problem down, but still don't know how to fix it ... The test was this: I took out all of the source files that have worked properly before, leaving approx 20 files (incl the file that the code had continually stopped on). This time, the 'problem' file worked ok, but the code STILL errored out on the same line (destrange.Value = sourceRange.Value) on the 16th file. So, it would seem that I'm defininely over-running some type of buffer, perhaps the clipboard? I've tried this code: Application.CutCopyMode = False at the end of each loop, to clear the clipboard .... as well as this code: Public Declare Function OpenClipboard Lib "user32" ( _ ByVal hwnd AsLong) As Long Public Declare Function CloseClipboard Lib "user32" () As Long Public Declare Function EmptyClipboard Lib "user32" () As Long Sub ClearClipboard() OpenClipboard (0&) EmptyClipboard CloseClipboard End Sub (via Frank Kabel) No luck on either one ... any more thoughts? hi ron - I have 30+ data files, one for each store ... these are identically structured. These files are opened one at a time and the desired range is 'copied' into the destination sheet within the Master Workbook. The 'getstore' code is used to identify the store file (ie the source wb) and the destination sheet (in the Master file) is Sheets(getstore). So, in the Master file, there are 30+ worksheets, one for each store ..... is that more clear? Thanks, ray |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need some more eyes to look at this ...
I see
If all the files are almost the same can you send me one private. I am curious what the problem is -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ray" wrote in message oups.com... On May 7, 1:12 pm, "Ron de Bruin" wrote: You can also try it with PasteSpecial Values and see if you have the same problem -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in .. . I not understand the code that you use now You loop through the files and copy the columns(27) to the same place each time ? Is that correct ? Tell me what you want then I can make another example for you that you can try -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ray" wrote in ooglegroups.com... On May 7, 11:10 am, "Ron de Bruin" wrote: Hi Ray Maybe there is a problem with the file ? Try one of the code samples on this pagehttp://www.rondebruin.nl/copy3.htm Or try the add-in (link on the page above) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ray" wrote in oglegroups.com... I'm not sure how to 'bump' my previous thread to the first page again (so more people will see it), so please take a look at this post: http://groups.google.com/group/micro...rogramming/bro... thanks, ray Hi Ron, thanks for your response ... I just ran a 'test' of sorts and I think I've narrowed the problem down, but still don't know how to fix it ... The test was this: I took out all of the source files that have worked properly before, leaving approx 20 files (incl the file that the code had continually stopped on). This time, the 'problem' file worked ok, but the code STILL errored out on the same line (destrange.Value = sourceRange.Value) on the 16th file. So, it would seem that I'm defininely over-running some type of buffer, perhaps the clipboard? I've tried this code: Application.CutCopyMode = False at the end of each loop, to clear the clipboard .... as well as this code: Public Declare Function OpenClipboard Lib "user32" ( _ ByVal hwnd AsLong) As Long Public Declare Function CloseClipboard Lib "user32" () As Long Public Declare Function EmptyClipboard Lib "user32" () As Long Sub ClearClipboard() OpenClipboard (0&) EmptyClipboard CloseClipboard End Sub (via Frank Kabel) No luck on either one ... any more thoughts? hi ron - I have 30+ data files, one for each store ... these are identically structured. These files are opened one at a time and the desired range is 'copied' into the destination sheet within the Master Workbook. The 'getstore' code is used to identify the store file (ie the source wb) and the destination sheet (in the Master file) is Sheets(getstore). So, in the Master file, there are 30+ worksheets, one for each store ..... is that more clear? Thanks, ray |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need some more eyes to look at this ...
On May 7, 2:01 pm, "Ron de Bruin" wrote:
I see If all the files are almost the same can you send me one private. I am curious what the problem is -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ray" wrote in ooglegroups.com... On May 7, 1:12 pm, "Ron de Bruin" wrote: You can also try it with PasteSpecial Values and see if you have the same problem -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in .. . I not understand the code that you use now You loop through the files and copy the columns(27) to the same place each time ? Is that correct ? Tell me what you want then I can make another example for you that you can try -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ray" wrote in ooglegroups.com... On May 7, 11:10 am, "Ron de Bruin" wrote: Hi Ray Maybe there is a problem with the file ? Try one of the code samples on this pagehttp://www.rondebruin.nl/copy3.htm Or try the add-in (link on the page above) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ray" wrote in oglegroups.com... I'm not sure how to 'bump' my previous thread to the first page again (so more people will see it), so please take a look at this post: http://groups.google.com/group/micro...rogramming/bro... thanks, ray Hi Ron, thanks for your response ... I just ran a 'test' of sorts and I think I've narrowed the problem down, but still don't know how to fix it ... The test was this: I took out all of the source files that have worked properly before, leaving approx 20 files (incl the file that the code had continually stopped on). This time, the 'problem' file worked ok, but the code STILL errored out on the same line (destrange.Value = sourceRange.Value) on the 16th file. So, it would seem that I'm defininely over-running some type of buffer, perhaps the clipboard? I've tried this code: Application.CutCopyMode = False at the end of each loop, to clear the clipboard .... as well as this code: Public Declare Function OpenClipboard Lib "user32" ( _ ByVal hwnd AsLong) As Long Public Declare Function CloseClipboard Lib "user32" () As Long Public Declare Function EmptyClipboard Lib "user32" () As Long Sub ClearClipboard() OpenClipboard (0&) EmptyClipboard CloseClipboard End Sub (via Frank Kabel) No luck on either one ... any more thoughts? hi ron - I have 30+ data files, one for each store ... these are identically structured. These files are opened one at a time and the desired range is 'copied' into the destination sheet within the Master Workbook. The 'getstore' code is used to identify the store file (ie the source wb) and the destination sheet (in the Master file) is Sheets(getstore). So, in the Master file, there are 30+ worksheets, one for each store ..... is that more clear? Thanks, ray Hi Ron - I just email the files to you .... I appreciate your time! //ray |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need some more eyes to look at this ...
This is working for Ray
I See if I can't find out why the other code is not working If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum), 0) ' Isolates the store number from the workbook name getstore = Left(mybook.Name, 3) Set sourceRange = mybook.Sheets("Store SRA").Range("F:AF").EntireColumn Set destrange = basebook.Sheets(getstore).Range("A1") sourceRange.Copy With destrange .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End With mybook.Close savechanges:=False Next Fnum End If -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ray" wrote in message ups.com... On May 7, 2:01 pm, "Ron de Bruin" wrote: I see If all the files are almost the same can you send me one private. I am curious what the problem is -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ray" wrote in ooglegroups.com... On May 7, 1:12 pm, "Ron de Bruin" wrote: You can also try it with PasteSpecial Values and see if you have the same problem -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in .. . I not understand the code that you use now You loop through the files and copy the columns(27) to the same place each time ? Is that correct ? Tell me what you want then I can make another example for you that you can try -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ray" wrote in ooglegroups.com... On May 7, 11:10 am, "Ron de Bruin" wrote: Hi Ray Maybe there is a problem with the file ? Try one of the code samples on this pagehttp://www.rondebruin.nl/copy3.htm Or try the add-in (link on the page above) -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm "Ray" wrote in oglegroups.com... I'm not sure how to 'bump' my previous thread to the first page again (so more people will see it), so please take a look at this post: http://groups.google.com/group/micro...rogramming/bro... thanks, ray Hi Ron, thanks for your response ... I just ran a 'test' of sorts and I think I've narrowed the problem down, but still don't know how to fix it ... The test was this: I took out all of the source files that have worked properly before, leaving approx 20 files (incl the file that the code had continually stopped on). This time, the 'problem' file worked ok, but the code STILL errored out on the same line (destrange.Value = sourceRange.Value) on the 16th file. So, it would seem that I'm defininely over-running some type of buffer, perhaps the clipboard? I've tried this code: Application.CutCopyMode = False at the end of each loop, to clear the clipboard .... as well as this code: Public Declare Function OpenClipboard Lib "user32" ( _ ByVal hwnd AsLong) As Long Public Declare Function CloseClipboard Lib "user32" () As Long Public Declare Function EmptyClipboard Lib "user32" () As Long Sub ClearClipboard() OpenClipboard (0&) EmptyClipboard CloseClipboard End Sub (via Frank Kabel) No luck on either one ... any more thoughts? hi ron - I have 30+ data files, one for each store ... these are identically structured. These files are opened one at a time and the desired range is 'copied' into the destination sheet within the Master Workbook. The 'getstore' code is used to identify the store file (ie the source wb) and the destination sheet (in the Master file) is Sheets(getstore). So, in the Master file, there are 30+ worksheets, one for each store ..... is that more clear? Thanks, ray Hi Ron - I just email the files to you .... I appreciate your time! //ray |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need a fresh pair of eyes | Excel Worksheet Functions | |||
| Fresh pair of Eyes required | Excel Worksheet Functions | |||
| My eyes Are Tiered, Forehead Bruised, My Desk Dented.!! Help??? | Excel Discussion (Misc queries) | |||
| Fonts Too Small or Eyes Too Big? | New Users to Excel | |||
| Keeping Prying eyes out | Excel Programming | |||
Linear Mode
