|
Related Topics:
| runtime error 3021 and user permissions - I have a VB6 that uses ADO 2.7 to access an database. I have found on Windows XP and 2000, that if the user is not part of the group, they receive: error 3021. Either BOF or EOF is true, or the current record..
updatable ado-recordset - Hi, In the code below i have an updatable However it is not working how i thought it would be. Maybe someone can help me out here? The global (Thanks to Brendan): Option Compare Database Option Explicit Public mconn As..
ADO Connection Access Database VB - I have been accessing a database on a local drive thru an ADO The following is the code that I have been using. Security The problem is now I have..
DataReport PageBreak - Hi everyone, I would like to know how to force pagebreak in a for example after 5 records printed. Thank you all
Recordset-adding to a Listview - I've got a weird problem - - - it must be something simple, but for the life of me, I must be missing it... I've got a recordset - and I'm trying to add the results into a listview, but, instead of adding each record after the first line of..
|
|
|
Next: Where can I find to download
|
| Author |
Message |
External
Since: Feb 09, 2005
Posts: 5
|
(Msg. 1) Posted: Wed Feb 09, 2005 11:11 am
Post subject: VBA excel using ADO
Archived from groups: microsoft>public>vb>database>ado (more info?)
|
|
|
I am trying to get information from one excel file to the current one. I have
to select a couple of columns and import onto the current excel file.
With cmdCommand
.CommandText = "SELECT `'Sheet2$'`.Step_Index, `'Sheet2$'`.`Voltage(V)`"
& Chr(13) & "" & Chr(10) & "FROM `" & Left(SourceFile, Len(SourceFile) - 4) &
"`.`'Sheet2$'` `'Sheet2$'`"
.CommandType = adCmdText
.Execute
End With
When I hard-code the name of Sheet2, the statement works. I don't know how
to replace "Sheet2" so it will be generic and work with all the files of the
same format but with different names for Sheet2. I know excel has one object
that let's you reference specific sheets by using Sheets(1).Select |
|
| Back to top |
|
 | |
External
Since: Sep 02, 2003
Posts: 236
|
(Msg. 2) Posted: Wed Feb 09, 2005 2:56 pm
Post subject: Re: VBA excel using ADO [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Wed, 9 Feb 2005 10:11:06 -0800, "Needy" <Needy RemoveThis @discussions.microsoft.com> wrote:
¤ I am trying to get information from one excel file to the current one. I have
¤ to select a couple of columns and import onto the current excel file.
¤
¤ With cmdCommand
¤ .CommandText = "SELECT `'Sheet2$'`.Step_Index, `'Sheet2$'`.`Voltage(V)`"
¤ & Chr(13) & "" & Chr(10) & "FROM `" & Left(SourceFile, Len(SourceFile) - 4) &
¤ "`.`'Sheet2$'` `'Sheet2$'`"
¤ .CommandType = adCmdText
¤ .Execute
¤ End With
¤
¤ When I hard-code the name of Sheet2, the statement works. I don't know how
¤ to replace "Sheet2" so it will be generic and work with all the files of the
¤ same format but with different names for Sheet2. I know excel has one object
¤ that let's you reference specific sheets by using Sheets(1).Select
Can't you just use a variable name to hold the name of the sheet and then build your command text in
code? Here is an example:
strSheetName = "Sheet5"
strSQL = "SELECT * INTO [Excel 8.0;DATABASE=E:\My Documents\Book10.xls;HDR=NO;IMEX=1]." &
strSheetName & " FROM [Sheet2$]"
Paul ~~~ pclement RemoveThis @ameritech.net
Microsoft MVP (Visual Basic) |
|
| Back to top |
|
| |
External
Since: Feb 09, 2005
Posts: 5
|
(Msg. 3) Posted: Wed Feb 09, 2005 11:13 pm
Post subject: Re: VBA excel using ADO [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Thanks Paul, but I have to run tens, if not hundreds, of files against it and
the names of the second sheet differ depending upon which channel on the
equipment the data got recorded. Having to get the name before running the
macro is possible, but could get tedious. >> Stay informed about: VBA excel using ADO |
|
| Back to top |
|
| |
External
Since: Feb 09, 2005
Posts: 5
|
(Msg. 4) Posted: Thu Feb 10, 2005 11:19 am
Post subject: Re: VBA excel using ADO [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
is there a way to select all columns from ALL worksheets of an excel
document, then just print the two columns by column numbers from the
recordset that I need and work from them? >> Stay informed about: VBA excel using ADO |
|
| Back to top |
|
| |
External
Since: Aug 31, 2003
Posts: 30
|
(Msg. 5) Posted: Thu Feb 10, 2005 11:40 am
Post subject: Re: VBA excel using ADO [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"Needy" <Needy.TakeThisOut@discussions.microsoft.com> wrote in message
news:A789E8B4-33D9-4D15-8501-02A590A37A0E@microsoft.com...
>I am trying to get information from one excel file to the current one. I
>have
> to select a couple of columns and import onto the current excel file.
>
> With cmdCommand
> .CommandText = "SELECT `'Sheet2$'`.Step_Index,
> `'Sheet2$'`.`Voltage(V)`"
> & Chr(13) & "" & Chr(10) & "FROM `" & Left(SourceFile, Len(SourceFile) -
> 4) &
> "`.`'Sheet2$'` `'Sheet2$'`"
> .CommandType = adCmdText
> .Execute
> End With
>
> When I hard-code the name of Sheet2, the statement works. I don't know how
> to replace "Sheet2" so it will be generic and work with all the files of
> the
> same format but with different names for Sheet2. I know excel has one
> object
> that let's you reference specific sheets by using Sheets(1).Select
>
So you want to extract the name of the second worksheet in any given
workbook?
Application.ActiveWorkbook.Sheets(2).Name
Where Application is an object of type Excel.Application.
-Mark<!-- ~MESSAGE_AFTER~ --> >> Stay informed about: VBA excel using ADO |
|
| Back to top |
|
| |
External
Since: Feb 09, 2005
Posts: 5
|
(Msg. 6) Posted: Thu Feb 10, 2005 12:01 pm
Post subject: Re: VBA excel using ADO [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Sep 02, 2003
Posts: 236
|
(Msg. 7) Posted: Fri Feb 11, 2005 2:40 pm
Post subject: Re: VBA excel using ADO [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Thu, 10 Feb 2005 10:19:01 -0800, "Needy" <Needy.RemoveThis@discussions.microsoft.com> wrote:
¤ is there a way to select all columns from ALL worksheets of an excel
¤ document, then just print the two columns by column numbers from the
¤ recordset that I need and work from them?
Would it help if you could get all of the Worksheet names in the Workbook programmatically?
I'm still not sure exactly what you are trying to do.
Paul ~~~ pclement.RemoveThis@ameritech.net
Microsoft MVP (Visual Basic) >> Stay informed about: VBA excel using ADO |
|
| Back to top |
|
| |
External
Since: Sep 02, 2003
Posts: 236
|
(Msg. 8) Posted: Fri Feb 11, 2005 5:43 pm
Post subject: Re: VBA excel using ADO [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Thu, 10 Feb 2005 10:19:01 -0800, "Needy" <Needy DeleteThis @discussions.microsoft.com> wrote:
If you need to retrieve a sheet by it's ordinal position in the Workbook you will probably need to
use DAO. Worst case scenario is Excel automation which I'm sure you probably want to avoid.
ADO doesn't return the Worksheets in the order they appear in the Workbook. Here is some DAO code
that returns them in the proper order:
Sub ListExcelTablesDAO()
Dim xlWB As DAO.Database
Dim tbl As DAO.TableDef
Set xlWB = OpenDatabase("e:\My Documents\Book10.xls", False, True, "Excel 8.0;")
For Each tbl In xlWB.TableDefs
Debug.Print tbl.NAME
Next tbl
'or
Debug.Print xlWB.TableDefs(1).NAME 'returns name of second Worksheet
xlWB.Close
End Sub
Paul ~~~ pclement DeleteThis @ameritech.net
Microsoft MVP (Visual Basic) >> Stay informed about: VBA excel using ADO |
|
| Back to top |
|
| |
External
Since: Aug 31, 2003
Posts: 30
|
(Msg. 9) Posted: Sat Feb 12, 2005 9:54 pm
Post subject: Re: VBA excel using ADO [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
"Needy" <Needy RemoveThis @discussions.microsoft.com> wrote in message
news:4DBDDC99-FBCC-45CF-873B-32EE55E817E5@microsoft.com...
> Yes, but not for the current workbook. The workbook that I'm creating an
> ADODB connection for in my current workbook.
When you load a workbook file it becomes active, so get a reference to it
then, the reference will still be valid for that particular workbook after
it's not the active one anymore.
If you were looking for a way to do this without loading/obtaining a
reference to the workbook[s] in question, sorry, I'm fresh out of options.
You'll need to open each one, extract the name, close it, then construct and
execute your distributed query.
-Mark<!-- ~MESSAGE_AFTER~ --> >> Stay informed about: VBA excel using ADO |
|
| Back to top |
|
| |
External
Since: Feb 09, 2005
Posts: 5
|
(Msg. 10) Posted: Sat Feb 19, 2005 1:41 pm
Post subject: Re: VBA excel using ADO [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
|
FAQ
Search
Profile
Private Messages
Log in
