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

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

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

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

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

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

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

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

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

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