Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.usedrange in closed workbook
[This followup was posted to microsoft.public.excel.programming and a copy was sent to the cited author.]
G'day there One & All, I'm currently experimenting with a couple of different methods to import data from various workbooks into a single workbook for various operations to be conducted upon it. I'm currently just copying the source worksheets with this code: srcPg.copy after:=tgtPg ...and it's working fine. At least it was until some of my clients started to implement their own techniques in their own workbooks (how DARE they!!!). I'm finding now when I cycle through my array of workbook titles, obtained from a GetOpenFileName dialogue, open them in turn, and import Sheet1 from each; that I get dialogues asking me about updating links, and/or conflicting names from where different clients have built formulae and have used the rather imaginative "Range1" in their sheets. Hence, the first imports OK, but from there on in I get Name Conflict errors (at least, I think that's what they were called). I've also tried copying the UsedRange to the clipboard and then using PasteSpecial to paste only the values and formatting, but that wasn't quite as successful as I'd hoped. Trouble is that I can't now remember why not. I'll have to give it another go to find out what went wrong there. In the meantime I thought I'd investigate the technique of leaving the workbooks closed and forming links which I found on a developer's site somewhere. The problem is that I don't know the extent of the data in each sheet that I need to copy. The data is consistently on Sheet1, and also a consistent number of columns. However the number of rows vary. Is there a way to determine the UsedRange from a closed workbook? My experiments to date have all met with failure, however that's just as likely to be my poor programming skills rather than an inability of XL to perform the calculation. Hope to hear back soonish Thanks to you all Ken McLennan Qld, Australia |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.usedrange in closed workbook
I don't see why PasteSpecial Values shouldn't work (unless you have merged
cells) -- Regards, Tom Ogilvy "Ken McLennan" wrote in message ... [This followup was posted to microsoft.public.excel.programming and a copy was sent to the cited author.] G'day there One & All, I'm currently experimenting with a couple of different methods to import data from various workbooks into a single workbook for various operations to be conducted upon it. I'm currently just copying the source worksheets with this code: srcPg.copy after:=tgtPg ...and it's working fine. At least it was until some of my clients started to implement their own techniques in their own workbooks (how DARE they!!!). I'm finding now when I cycle through my array of workbook titles, obtained from a GetOpenFileName dialogue, open them in turn, and import Sheet1 from each; that I get dialogues asking me about updating links, and/or conflicting names from where different clients have built formulae and have used the rather imaginative "Range1" in their sheets. Hence, the first imports OK, but from there on in I get Name Conflict errors (at least, I think that's what they were called). I've also tried copying the UsedRange to the clipboard and then using PasteSpecial to paste only the values and formatting, but that wasn't quite as successful as I'd hoped. Trouble is that I can't now remember why not. I'll have to give it another go to find out what went wrong there. In the meantime I thought I'd investigate the technique of leaving the workbooks closed and forming links which I found on a developer's site somewhere. The problem is that I don't know the extent of the data in each sheet that I need to copy. The data is consistently on Sheet1, and also a consistent number of columns. However the number of rows vary. Is there a way to determine the UsedRange from a closed workbook? My experiments to date have all met with failure, however that's just as likely to be my poor programming skills rather than an inability of XL to perform the calculation. Hope to hear back soonish Thanks to you all Ken McLennan Qld, Australia |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
.usedrange in closed workbook
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
.usedrange in closed workbook
G'day there again, Tom,
I don't see why PasteSpecial Values shouldn't work (unless you have merged cells) I'm not sure now myself why it didn't work. I can't even recall what messages I got. I intend to have a play with it tonight to see what's going on. I've now gone back and had a look at that version of my code. Guess what? It wasn't a bug in Microsoft's code it was a typo on my part (dammit!!). Actually it was a couple of instances of the same typo - that's what happens when you mistype something and then copy & paste it. I had this loop: With ThisWorkbook .Sheets.Add after:=.Sheets(.Sheets.Count) .Sheets(.Sheets.Count).Name = newName(srcBk.Name) srcPg.UsedRange.Copy .Sheets(.Sheets.Count).Range("A1").PasteSpecial _ Paste:=xlPasteValuesAndNumberFormats .Sheets(.Sheets.Count).Range("A1").PasteSpecial _ Paste:=xlPasteFormats Application.CutCopyMode = False End With Except that where I use ".Sheets(.Sheets.Count)" I had "Sheets (Sheets.Count)" behind the "after:=" and ".Sheets(Sheets.Count)" everywhere else. Now that I have periods in where they're supposed to be it works fine. Only thing now is that after pasting, all of the pasted range stays selected. I've tried using range("A1").select, but without success. The entire pasted range still remains selected. I think I may have to select a cell outside the range, or activate the sheet first. Any suggestions? It's not really a biggy at the moment, but I'm sure to find a client who'll cut rather than copy and things will stuff up from there. See ya Thanks for your help Ken McLennan Qld, Australia |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
.usedrange in closed workbook
range("A1").Select works for me. range("A1").Activate only changes the
activecell - but select should reduce the selection. -- Regards, Tom Ogilvy "Ken McLennan" wrote in message ... G'day there again, Tom, I don't see why PasteSpecial Values shouldn't work (unless you have merged cells) I'm not sure now myself why it didn't work. I can't even recall what messages I got. I intend to have a play with it tonight to see what's going on. I've now gone back and had a look at that version of my code. Guess what? It wasn't a bug in Microsoft's code it was a typo on my part (dammit!!). Actually it was a couple of instances of the same typo - that's what happens when you mistype something and then copy & paste it. I had this loop: With ThisWorkbook .Sheets.Add after:=.Sheets(.Sheets.Count) .Sheets(.Sheets.Count).Name = newName(srcBk.Name) srcPg.UsedRange.Copy .Sheets(.Sheets.Count).Range("A1").PasteSpecial _ Paste:=xlPasteValuesAndNumberFormats .Sheets(.Sheets.Count).Range("A1").PasteSpecial _ Paste:=xlPasteFormats Application.CutCopyMode = False End With Except that where I use ".Sheets(.Sheets.Count)" I had "Sheets (Sheets.Count)" behind the "after:=" and ".Sheets(Sheets.Count)" everywhere else. Now that I have periods in where they're supposed to be it works fine. Only thing now is that after pasting, all of the pasted range stays selected. I've tried using range("A1").select, but without success. The entire pasted range still remains selected. I think I may have to select a cell outside the range, or activate the sheet first. Any suggestions? It's not really a biggy at the moment, but I'm sure to find a client who'll cut rather than copy and things will stuff up from there. See ya Thanks for your help Ken McLennan Qld, Australia |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
.usedrange in closed workbook
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
.usedrange in closed workbook
Try this
http://www.rondebruin.nl/copy3.htm Read the tips section about the workbook open arguments -- Regards Ron de Bruin http://www.rondebruin.nl "Ken McLennan" wrote in message ... [This followup was posted to microsoft.public.excel.programming and a copy was sent to the cited author.] G'day there One & All, I'm currently experimenting with a couple of different methods to import data from various workbooks into a single workbook for various operations to be conducted upon it. I'm currently just copying the source worksheets with this code: srcPg.copy after:=tgtPg ...and it's working fine. At least it was until some of my clients started to implement their own techniques in their own workbooks (how DARE they!!!). I'm finding now when I cycle through my array of workbook titles, obtained from a GetOpenFileName dialogue, open them in turn, and import Sheet1 from each; that I get dialogues asking me about updating links, and/or conflicting names from where different clients have built formulae and have used the rather imaginative "Range1" in their sheets. Hence, the first imports OK, but from there on in I get Name Conflict errors (at least, I think that's what they were called). I've also tried copying the UsedRange to the clipboard and then using PasteSpecial to paste only the values and formatting, but that wasn't quite as successful as I'd hoped. Trouble is that I can't now remember why not. I'll have to give it another go to find out what went wrong there. In the meantime I thought I'd investigate the technique of leaving the workbooks closed and forming links which I found on a developer's site somewhere. The problem is that I don't know the extent of the data in each sheet that I need to copy. The data is consistently on Sheet1, and also a consistent number of columns. However the number of rows vary. Is there a way to determine the UsedRange from a closed workbook? My experiments to date have all met with failure, however that's just as likely to be my poor programming skills rather than an inability of XL to perform the calculation. Hope to hear back soonish Thanks to you all Ken McLennan Qld, Australia |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
.usedrange in closed workbook
You are welcome Ken
-- Regards Ron de Bruin http://www.rondebruin.nl "Ken McLennan" wrote in message ... G'day there One & All, In article , says... Try this http://www.rondebruin.nl/copy3.htm Read the tips section about the workbook open arguments For those who were following this thread, I HAVE thanked Ron for his assistance, but just realised that I hit Reply, and not Follow Up in my Gravity NewsReader. Hence, you won't see my comments about what a great link that is above. There's a stack of information on that page, and if you follow the link to Ron's Tips Page, there are bucketloads of helpful stuff! I highly recommend it. Thanks very muchly Ron, and now Thanks Publicly too. See ya Ken McLennan Qld, Australia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference to closed workbook | Excel Discussion (Misc queries) | |||
Closing Hidden Workbook when Active Workbook is Closed | Excel Programming | |||
Value from a closed workbook | Excel Discussion (Misc queries) | |||
copy worksheet from closed workbook to active workbook using vba | Excel Worksheet Functions | |||
Getting value from closed workbook | Excel Programming |