| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
import data without openning excel workbook
hi All,
How to import a data from a excel workbook without opening the book. lets say book name "mybook" location is:"C:\Documents and Settings\BahadiAkcan\My Documents" using above file can I make a match or vlookup function in a code? thanks for your help -- Regards, Baha |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
import data without openning excel workbook
Here is the basic syntax. Adapt to your vlookup and modify to suit
=SUMPRODUCT(('C:\yourfolder\[yourfile.xls]yoursheet'!$E$7:$E$1000=1)*1) -- Don Guillett SalesAid Software "Baha" wrote in message ... hi All, How to import a data from a excel workbook without opening the book. lets say book name "mybook" location is:"C:\Documents and Settings\BahadiAkcan\My Documents" using above file can I make a match or vlookup function in a code? thanks for your help -- Regards, Baha |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
import data without openning excel workbook
Thank you for the tip,but i was trying to put in a VBA code
so far I tried this but couldn`t figure out the proper one sub getTheData() dim x as long x = Application.WorksheetFunction.VLookup(D7, "C:\Documents and Settings\BahadiAkcan\My Documents\Book1hhhfhfh.xls" .Sheet1.Range("a1:d33"), 3, 0) msgbox x end sub can you give me a hint please? -- Regards, Baha "Don Guillett" wrote: Here is the basic syntax. Adapt to your vlookup and modify to suit =SUMPRODUCT(('C:\yourfolder\[yourfile.xls]yoursheet'!$E$7:$E$1000=1)*1) -- Don Guillett SalesAid Software "Baha" wrote in message ... hi All, How to import a data from a excel workbook without opening the book. lets say book name "mybook" location is:"C:\Documents and Settings\BahadiAkcan\My Documents" using above file can I make a match or vlookup function in a code? thanks for your help -- Regards, Baha |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
import data without openning excel workbook
hi,
i think I get my answer from other site. Thank you any way.Any one intrested this question;here is the link that u can get the answer http://www.rondebruin.nl/copy7.htm -- Regards, Baha "Baha" wrote: Thank you for the tip,but i was trying to put in a VBA code so far I tried this but couldn`t figure out the proper one sub getTheData() dim x as long x = Application.WorksheetFunction.VLookup(D7, "C:\Documents and Settings\BahadiAkcan\My Documents\Book1hhhfhfh.xls" .Sheet1.Range("a1:d33"), 3, 0) msgbox x end sub can you give me a hint please? -- Regards, Baha "Don Guillett" wrote: Here is the basic syntax. Adapt to your vlookup and modify to suit =SUMPRODUCT(('C:\yourfolder\[yourfile.xls]yoursheet'!$E$7:$E$1000=1)*1) -- Don Guillett SalesAid Software "Baha" wrote in message ... hi All, How to import a data from a excel workbook without opening the book. lets say book name "mybook" location is:"C:\Documents and Settings\BahadiAkcan\My Documents" using above file can I make a match or vlookup function in a code? thanks for your help -- Regards, Baha |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
import data without openning excel workbook
Technically, that is putting a formula in a cell and letting the Excel
linking mechanism retrieve the data, not getting it from VBA as you specified. -- Regards, Tom Ogilvy "Baha" wrote in message ... hi, i think I get my answer from other site. Thank you any way.Any one intrested this question;here is the link that u can get the answer http://www.rondebruin.nl/copy7.htm -- Regards, Baha "Baha" wrote: Thank you for the tip,but i was trying to put in a VBA code so far I tried this but couldn`t figure out the proper one sub getTheData() dim x as long x = Application.WorksheetFunction.VLookup(D7, "C:\Documents and Settings\BahadiAkcan\My Documents\Book1hhhfhfh.xls" .Sheet1.Range("a1:d33"), 3, 0) msgbox x end sub can you give me a hint please? -- Regards, Baha "Don Guillett" wrote: Here is the basic syntax. Adapt to your vlookup and modify to suit =SUMPRODUCT(('C:\yourfolder\[yourfile.xls]yoursheet'!$E$7:$E$1000=1)*1) -- Don Guillett SalesAid Software "Baha" wrote in message ... hi All, How to import a data from a excel workbook without opening the book. lets say book name "mybook" location is:"C:\Documents and Settings\BahadiAkcan\My Documents" using above file can I make a match or vlookup function in a code? thanks for your help -- Regards, Baha |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
import data without openning excel workbook
after I use that code, my problem actually didnt fixed much.As you mentioned
I cannot use a function for closed workbook,so that code put entire column values(cos I have to select A:A),then now I wrote an other code to delete all cells which have "0" values which works very slow.Can u suggest,anything else to delete "0" value? here is my code Application.ScreenUpdating = False Dim cdel cdel = Application.WorksheetFunction.CountIf(Sheet1.Range ("a1:a30000"), "0") Do Until cdel = 0 Cells.Find(What:="0", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.EntireRow.Delete Shift:=xlUp cdel = cdel - 1 Loop Application.ScreenUpdating = True is it very ugly?:) -- Regards, Baha "Tom Ogilvy" wrote: Technically, that is putting a formula in a cell and letting the Excel linking mechanism retrieve the data, not getting it from VBA as you specified. -- Regards, Tom Ogilvy "Baha" wrote in message ... hi, i think I get my answer from other site. Thank you any way.Any one intrested this question;here is the link that u can get the answer http://www.rondebruin.nl/copy7.htm -- Regards, Baha "Baha" wrote: Thank you for the tip,but i was trying to put in a VBA code so far I tried this but couldn`t figure out the proper one sub getTheData() dim x as long x = Application.WorksheetFunction.VLookup(D7, "C:\Documents and Settings\BahadiAkcan\My Documents\Book1hhhfhfh.xls" .Sheet1.Range("a1:d33"), 3, 0) msgbox x end sub can you give me a hint please? -- Regards, Baha "Don Guillett" wrote: Here is the basic syntax. Adapt to your vlookup and modify to suit =SUMPRODUCT(('C:\yourfolder\[yourfile.xls]yoursheet'!$E$7:$E$1000=1)*1) -- Don Guillett SalesAid Software "Baha" wrote in message ... hi All, How to import a data from a excel workbook without opening the book. lets say book name "mybook" location is:"C:\Documents and Settings\BahadiAkcan\My Documents" using above file can I make a match or vlookup function in a code? thanks for your help -- Regards, Baha |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
import data without openning excel workbook
Nothing is ugly that works, some things are just a bit more 'elegant'. I'm
not sure this is going to be more elegant at all, but you might try it to see if it gives you a performance improvement. Change "Sheet1" to the name of the sheet the data is on in your workbook. Sub DeleteZeroRows() Dim anyOffset As Long With Sheets("Sheet1").Range("A1") For anyOffset = 29999 To 1 Step -1 If .Offset(anyOffset, 0) = 0 Then .Offset(anyOffset, 0).EntireRow.Delete End If Next End With End Sub "Baha" wrote: after I use that code, my problem actually didnt fixed much.As you mentioned I cannot use a function for closed workbook,so that code put entire column values(cos I have to select A:A),then now I wrote an other code to delete all cells which have "0" values which works very slow.Can u suggest,anything else to delete "0" value? here is my code Application.ScreenUpdating = False Dim cdel cdel = Application.WorksheetFunction.CountIf(Sheet1.Range ("a1:a30000"), "0") Do Until cdel = 0 Cells.Find(What:="0", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.EntireRow.Delete Shift:=xlUp cdel = cdel - 1 Loop Application.ScreenUpdating = True is it very ugly?:) -- Regards, Baha "Tom Ogilvy" wrote: Technically, that is putting a formula in a cell and letting the Excel linking mechanism retrieve the data, not getting it from VBA as you specified. -- Regards, Tom Ogilvy "Baha" wrote in message ... hi, i think I get my answer from other site. Thank you any way.Any one intrested this question;here is the link that u can get the answer http://www.rondebruin.nl/copy7.htm -- Regards, Baha "Baha" wrote: Thank you for the tip,but i was trying to put in a VBA code so far I tried this but couldn`t figure out the proper one sub getTheData() dim x as long x = Application.WorksheetFunction.VLookup(D7, "C:\Documents and Settings\BahadiAkcan\My Documents\Book1hhhfhfh.xls" .Sheet1.Range("a1:d33"), 3, 0) msgbox x end sub can you give me a hint please? -- Regards, Baha "Don Guillett" wrote: Here is the basic syntax. Adapt to your vlookup and modify to suit =SUMPRODUCT(('C:\yourfolder\[yourfile.xls]yoursheet'!$E$7:$E$1000=1)*1) -- Don Guillett SalesAid Software "Baha" wrote in message ... hi All, How to import a data from a excel workbook without opening the book. lets say book name "mybook" location is:"C:\Documents and Settings\BahadiAkcan\My Documents" using above file can I make a match or vlookup function in a code? thanks for your help -- Regards, Baha |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
import data without openning excel workbook
Thank you very much for the code. I took your advice and write several
different code ( they all looks ugly but working:) Now reduce these whole process from 3 min 45 sec to 20 sec.Thats fine for me. I really love this discussion group, I learned a lot of things this weekend.Thanks to you guys, -- Regards, Baha "JLatham" wrote: Nothing is ugly that works, some things are just a bit more 'elegant'. I'm not sure this is going to be more elegant at all, but you might try it to see if it gives you a performance improvement. Change "Sheet1" to the name of the sheet the data is on in your workbook. Sub DeleteZeroRows() Dim anyOffset As Long With Sheets("Sheet1").Range("A1") For anyOffset = 29999 To 1 Step -1 If .Offset(anyOffset, 0) = 0 Then .Offset(anyOffset, 0).EntireRow.Delete End If Next End With End Sub "Baha" wrote: after I use that code, my problem actually didnt fixed much.As you mentioned I cannot use a function for closed workbook,so that code put entire column values(cos I have to select A:A),then now I wrote an other code to delete all cells which have "0" values which works very slow.Can u suggest,anything else to delete "0" value? here is my code Application.ScreenUpdating = False Dim cdel cdel = Application.WorksheetFunction.CountIf(Sheet1.Range ("a1:a30000"), "0") Do Until cdel = 0 Cells.Find(What:="0", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.EntireRow.Delete Shift:=xlUp cdel = cdel - 1 Loop Application.ScreenUpdating = True is it very ugly?:) -- Regards, Baha "Tom Ogilvy" wrote: Technically, that is putting a formula in a cell and letting the Excel linking mechanism retrieve the data, not getting it from VBA as you specified. -- Regards, Tom Ogilvy "Baha" wrote in message ... hi, i think I get my answer from other site. Thank you any way.Any one intrested this question;here is the link that u can get the answer http://www.rondebruin.nl/copy7.htm -- Regards, Baha "Baha" wrote: Thank you for the tip,but i was trying to put in a VBA code so far I tried this but couldn`t figure out the proper one sub getTheData() dim x as long x = Application.WorksheetFunction.VLookup(D7, "C:\Documents and Settings\BahadiAkcan\My Documents\Book1hhhfhfh.xls" .Sheet1.Range("a1:d33"), 3, 0) msgbox x end sub can you give me a hint please? -- Regards, Baha "Don Guillett" wrote: Here is the basic syntax. Adapt to your vlookup and modify to suit =SUMPRODUCT(('C:\yourfolder\[yourfile.xls]yoursheet'!$E$7:$E$1000=1)*1) -- Don Guillett SalesAid Software "Baha" wrote in message ... hi All, How to import a data from a excel workbook without opening the book. lets say book name "mybook" location is:"C:\Documents and Settings\BahadiAkcan\My Documents" using above file can I make a match or vlookup function in a code? thanks for your help -- Regards, Baha |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
import data without openning excel workbook
Glad to have been of some small assistance to you. And thank you for the
feedback. I, too, continue to learn from what others post in this forum and others like it. "Baha" wrote: Thank you very much for the code. I took your advice and write several different code ( they all looks ugly but working:) Now reduce these whole process from 3 min 45 sec to 20 sec.Thats fine for me. I really love this discussion group, I learned a lot of things this weekend.Thanks to you guys, -- Regards, Baha "JLatham" wrote: Nothing is ugly that works, some things are just a bit more 'elegant'. I'm not sure this is going to be more elegant at all, but you might try it to see if it gives you a performance improvement. Change "Sheet1" to the name of the sheet the data is on in your workbook. Sub DeleteZeroRows() Dim anyOffset As Long With Sheets("Sheet1").Range("A1") For anyOffset = 29999 To 1 Step -1 If .Offset(anyOffset, 0) = 0 Then .Offset(anyOffset, 0).EntireRow.Delete End If Next End With End Sub "Baha" wrote: after I use that code, my problem actually didnt fixed much.As you mentioned I cannot use a function for closed workbook,so that code put entire column values(cos I have to select A:A),then now I wrote an other code to delete all cells which have "0" values which works very slow.Can u suggest,anything else to delete "0" value? here is my code Application.ScreenUpdating = False Dim cdel cdel = Application.WorksheetFunction.CountIf(Sheet1.Range ("a1:a30000"), "0") Do Until cdel = 0 Cells.Find(What:="0", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.EntireRow.Delete Shift:=xlUp cdel = cdel - 1 Loop Application.ScreenUpdating = True is it very ugly?:) -- Regards, Baha "Tom Ogilvy" wrote: Technically, that is putting a formula in a cell and letting the Excel linking mechanism retrieve the data, not getting it from VBA as you specified. -- Regards, Tom Ogilvy "Baha" wrote in message ... hi, i think I get my answer from other site. Thank you any way.Any one intrested this question;here is the link that u can get the answer http://www.rondebruin.nl/copy7.htm -- Regards, Baha "Baha" wrote: Thank you for the tip,but i was trying to put in a VBA code so far I tried this but couldn`t figure out the proper one sub getTheData() dim x as long x = Application.WorksheetFunction.VLookup(D7, "C:\Documents and Settings\BahadiAkcan\My Documents\Book1hhhfhfh.xls" .Sheet1.Range("a1:d33"), 3, 0) msgbox x end sub can you give me a hint please? -- Regards, Baha "Don Guillett" wrote: Here is the basic syntax. Adapt to your vlookup and modify to suit =SUMPRODUCT(('C:\yourfolder\[yourfile.xls]yoursheet'!$E$7:$E$1000=1)*1) -- Don Guillett SalesAid Software "Baha" wrote in message ... hi All, How to import a data from a excel workbook without opening the book. lets say book name "mybook" location is:"C:\Documents and Settings\BahadiAkcan\My Documents" using above file can I make a match or vlookup function in a code? thanks for your help -- Regards, Baha |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
import data without openning excel workbook
Thank you very much. That's the code I'm looking for.
This discussion group is wonderful! ...... save me a lot of time in searching and sieving through informative material. Cheers! "Baha" wrote in message ... hi, i think I get my answer from other site. Thank you any way.Any one intrested this question;here is the link that u can get the answer http://www.rondebruin.nl/copy7.htm -- Regards, Baha "Baha" wrote: Thank you for the tip,but i was trying to put in a VBA code so far I tried this but couldn`t figure out the proper one sub getTheData() dim x as long x = Application.WorksheetFunction.VLookup(D7, "C:\Documents and Settings\BahadiAkcan\My Documents\Book1hhhfhfh.xls" .Sheet1.Range("a1:d33"), 3, 0) msgbox x end sub can you give me a hint please? -- Regards, Baha "Don Guillett" wrote: Here is the basic syntax. Adapt to your vlookup and modify to suit =SUMPRODUCT(('C:\yourfolder\[yourfile.xls]yoursheet'!$E$7:$E$1000=1)*1) -- Don Guillett SalesAid Software "Baha" wrote in message ... hi All, How to import a data from a excel workbook without opening the book. lets say book name "mybook" location is:"C:\Documents and Settings\BahadiAkcan\My Documents" using above file can I make a match or vlookup function in a code? thanks for your help -- Regards, Baha |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2007:Share Workbook with import live Data | Excel Worksheet Functions | |||
| Import/Copy some data from one workbook to a similar workbook | Excel Programming | |||
| i want to import data from one workbook to another and add a row | Excel Worksheet Functions | |||
| How do I import data from 1 workbook excel sheet into another? | New Users to Excel | |||
| Import Excel Data from another workbook or file | Excel Discussion (Misc queries) | |||
Linear Mode
