Need to read external workbook
 

Hi,

I am right now working Excel using VBA. I am using DAO for reading a
external sheet data. I am not getting the output. Can some one hel
me in fixing this problem. Following is the code snapshot:

Set rs = db.OpenRecordset("SELECT * from Instruction")
If rs Is Nothing Then
MsgBox "Can't open the file!", vbExclamation
ThisWorkbook.Name
db.Close
Set db = Nothing
Exit Sub
End If

Here 'rs' is returning null. I am trying to open a work sheet name
'Instruction' from an external workbook file. Could you please let m
know the exact syntax for accessing the sheet information using DAO.
And also how to refer column header in the 'SELECT' query if the colum
names have words separated by space. For example "Project Name" is th
column header name separated by space. And any good DAO using VBA lin
will also help me.

Thanks in advance!

Rav

--
smilysof
-----------------------------------------------------------------------
smilysoft's Profile: http://www.excelforum.com/member.php...fo&userid=3270
View this thread: http://www.excelforum.com/showthread.php?threadid=52529

Need to read external workbook
 

Ravo

I think you need the sheet name in brackets and append a dollar sign, like

SELECT * From [Instruction$]

I think you can put single quotes around the field names if they have
spaces, but I haven't tested it.

SELECT 'Project Name' From [Instructions$]

There's an example of using ADO, but not with as much detail as you'd like,
here

http://www.dailydoseofexcel.com/arch...ing-workbooks/

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

smilysoft wrote:
Hi,

I am right now working Excel using VBA. I am using DAO for reading an
external sheet data. I am not getting the output. Can some one help
me in fixing this problem. Following is the code snapshot:

Set rs = db.OpenRecordset("SELECT * from Instruction")
If rs Is Nothing Then
MsgBox "Can't open the file!", vbExclamation,
ThisWorkbook.Name
db.Close
Set db = Nothing
Exit Sub
End If

Here 'rs' is returning null. I am trying to open a work sheet named
'Instruction' from an external workbook file. Could you please let me
know the exact syntax for accessing the sheet information using DAO.
And also how to refer column header in the 'SELECT' query if the
column names have words separated by space. For example "Project
Name" is the column header name separated by space. And any good DAO
using VBA link will also help me.

Thanks in advance!

Ravo