Hello,

I would like to have your assistance on a problem of VBA. I recover
since Access a cross query that I insert in Excel. My problem is that I
make an external joint in my cross query and that it create me
automatically a field.

My Problem: I have a field which is named < in my query. It is
possible to remove this field in my Recordset?. In sum up, when my
recordset meets a data of this field then < it removes the data.

I can delete or hide the column but i would like to find an other
solution.

Here my code:

Sub CopyFromRecordset()
Dim Db1 As Database
Dim Rs1 As Recordset, Nb As Long
Dim Sh As Worksheet, Rg As Range, Nl As Range
Dim Chemin As String, Fichier As String

Dim bordure As MsoLineStyle
bordure = msoLineSingle

Set Sh = Worksheets("Module")
With Sh
Set Rg = .Range("A8")
End With

Set Db1 = DBEngine.OpenDatabase(ThisWorkbook.Path & "\MyDataBase.mdb")

Set Rs1 = Db1.OpenRecordset("REPORTING: Database", dbOpenDynaset)

Rg.CurrentRegion.Clear

If Rs1.EOF = False Then

Nb = Rs1.Fields.Count - 1

'-- I display the field name <--
For a = 0 To Nb
Rg(, 1 + a) = Rs1.Fields(a).Name
Next

Rg.Resize(, Nb + 1).Font.Bold = True

Rg.Offset(1).CopyFromRecordset Rs1

Else

MsgBox "No Record."
End If


Set Rg = Nothing: Set Sh = Nothing

Rs1.Close: Db1.Close

Set Rs1 = Nothing: Set Db1 = Nothing
End Sub


Thanks for your help

Keawee



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Let me see if I understand you.....

You are querying an Access database, and you need to input
the recordset into an Excel worksheet. You are having
difficulty in inputting the recordset into the worksheet
because the column names and the recordset field names do
not match. Is this your problem? Do I understand you
correctly?

If that is your problem, then I think all you need to do
is change the SELECT statement of your query. Make sure
that your SELECT statement matches the column names in
Excel. Example:

Query (from Access):SELECT Last, First, Address1, Phone
FROM tblUser...........

Column names (in Excel): Last, First, Address1, Phone

If this is not your problem, or I've been absolutely no
help to you......sorry. Try to explain again, and I'll
see if I can help.
-----Original Message-----
Hello,

I would like to have your assistance on a problem of VBA.
I recover
since Access a cross query that I insert in Excel. My
problem is that I
make an external joint in my cross query and that it
create me
automatically a field.

My Problem: I have a field which is named < in my
query. It is
possible to remove this field in my Recordset?. In sum
up, when my
recordset meets a data of this field then < it removes
the data.

I can delete or hide the column but i would like to find
an other
solution.

Here my code:

Sub CopyFromRecordset()
Dim Db1 As Database
Dim Rs1 As Recordset, Nb As Long
Dim Sh As Worksheet, Rg As Range, Nl As Range
Dim Chemin As String, Fichier As String

Dim bordure As MsoLineStyle
bordure = msoLineSingle

Set Sh = Worksheets("Module")
With Sh
Set Rg = .Range("A8")
End With

Set Db1 = DBEngine.OpenDatabase(ThisWorkbook.Path
& "\MyDataBase.mdb")

Set Rs1 = Db1.OpenRecordset("REPORTING: Database",
dbOpenDynaset)

Rg.CurrentRegion.Clear

If Rs1.EOF = False Then

Nb = Rs1.Fields.Count - 1

'-- I display the field name <--
For a = 0 To Nb
Rg(, 1 + a) = Rs1.Fields(a).Name
Next

Rg.Resize(, Nb + 1).Font.Bold = True

Rg.Offset(1).CopyFromRecordset Rs1

Else

MsgBox "No Record."
End If


Set Rg = Nothing: Set Sh = Nothing

Rs1.Close: Db1.Close

Set Rs1 = Nothing: Set Db1 = Nothing
End Sub


Thanks for your help

Keawee



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

.