By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,864 Members | 1,321 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,864 IT Pros & Developers. It's quick & easy.

Add rows to top of existing spreadsheet

P: n/a
How do I, in VBA from an access form module, add 5 rows to the top of a
spreadsheet called MySpreadsheet.xls. The worksheet is called MyWorksheet
and there is already data in the first 5 rows. I simply need to insert 5
blank rows at the top and move the rest of the data down so it starts at row
6.

dixie
Nov 13 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
Forget this one, I have figured it out myself.

dixie

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...
How do I, in VBA from an access form module, add 5 rows to the top of a
spreadsheet called MySpreadsheet.xls. The worksheet is called MyWorksheet
and there is already data in the first 5 rows. I simply need to insert 5
blank rows at the top and move the rest of the data down so it starts at
row 6.

dixie

Nov 13 '05 #2

P: n/a
Dixie,

That sounds very useful. Could you share it with us?

Hank

Nov 13 '05 #3

P: n/a
The code to add one line was simply

ActiveCell.EntireRow.Insert

I didn't have to worry about where I wanted to insert it as it was above the
first line, which must be the default, because it worked.

I simply repeated the line 5 times to insert 5 blank lines - not very
sophisticated, but that probably sums me up. :-)

dixie

"Hank" <ha********@aol.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Dixie,

That sounds very useful. Could you share it with us?

Hank

Nov 13 '05 #4

P: n/a

Just be aware that for this to work, the active cell where you want
the insert to occur at must be the selected first. If someone goes in
and moves the active cell to something other then A1, then you'll get
the inserted rows at the wrong place.

It may be smarter to either select the Active Cell yourself first, or
just select the Rows where you want the inserted rows to be yourself.

Rows("1:5").Select
Selection.Insert Shift:=-4121 '-4121 = xlDown

You may also want to be sure that the correct worksheet is selected,
before doing any of this.
On Sun, 25 Sep 2005 19:57:35 +1000, "Dixie" <di***@dogmail.com> wrote:
The code to add one line was simply
ActiveCell.EntireRow.Insert
I didn't have to worry about where I wanted to insert it as it was above the
first line, which must be the default, because it worked.
I simply repeated the line 5 times to insert 5 blank lines - not very
sophisticated, but that probably sums me up. :-) "Hank" <ha********@aol.com> wrote in message
news:11**********************@z14g2000cwz.googleg roups.com...
That sounds very useful. Could you share it with us?

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

Nov 13 '05 #5

P: n/a
I had put this line in first:
xl.Range("A1").select

What does the line:
Selection.Insert Shift:=-4121 '-4121 = xlDown
mean Chuck? Obviously, it inserts a blank line, but it seems a bit strange.

dixie

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:t3********************************@4ax.com...

Just be aware that for this to work, the active cell where you want
the insert to occur at must be the selected first. If someone goes in
and moves the active cell to something other then A1, then you'll get
the inserted rows at the wrong place.

It may be smarter to either select the Active Cell yourself first, or
just select the Rows where you want the inserted rows to be yourself.

Rows("1:5").Select
Selection.Insert Shift:=-4121 '-4121 = xlDown

You may also want to be sure that the correct worksheet is selected,
before doing any of this.
On Sun, 25 Sep 2005 19:57:35 +1000, "Dixie" <di***@dogmail.com> wrote:
The code to add one line was simply
ActiveCell.EntireRow.Insert
I didn't have to worry about where I wanted to insert it as it was above
the
first line, which must be the default, because it worked.
I simply repeated the line 5 times to insert 5 blank lines - not very
sophisticated, but that probably sums me up. :-)

"Hank" <ha********@aol.com> wrote in message
news:11**********************@z14g2000cwz.google groups.com...
That sounds very useful. Could you share it with us?

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

Nov 13 '05 #6

P: n/a

The "'-4121 = xlDown" part is a comment. It's just there to indicate
that -4121 is there as an explanation of the Excel constant "xlDown".

Whenever possible, it's actually best to use the Excel constants, as
they won't change across the various versions. Sadly, when using late
binding, that isn't possible.

On Sun, 25 Sep 2005 21:50:33 +1000, "Dixie" <di***@dogmail.com> wrote:
What does the line:
Selection.Insert Shift:=-4121 '-4121 = xlDown
mean Chuck? Obviously, it inserts a blank line, but it seems a bit strange. "Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:t3********************************@4ax.com.. .
Just be aware that for this to work, the active cell where you want
the insert to occur at must be the selected first. If someone goes in
and moves the active cell to something other then A1, then you'll get
the inserted rows at the wrong place.
It may be smarter to either select the Active Cell yourself first, or
just select the Rows where you want the inserted rows to be yourself.
Rows("1:5").Select
Selection.Insert Shift:=-4121 '-4121 = xlDown
You may also want to be sure that the correct worksheet is selected,
before doing any of this. On Sun, 25 Sep 2005 19:57:35 +1000, "Dixie" <di***@dogmail.com> wrote:
The code to add one line was simply
ActiveCell.EntireRow.Insert
I didn't have to worry about where I wanted to insert it as it was above
the
first line, which must be the default, because it worked.
I simply repeated the line 5 times to insert 5 blank lines - not very
sophisticated, but that probably sums me up. :-)

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

Nov 13 '05 #7

P: n/a
One more thing I need to know. Is it possible to save the spreadsheet file
as a tab delimited text file from within Access after these operations have
been done? The end result of all this needs to be a tab delimted text file
with the same file name and obviously .txt extension. I could save it
manually, but it would be nice if it were possible to do it in vba.

dixie

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:55********************************@4ax.com...

The "'-4121 = xlDown" part is a comment. It's just there to indicate
that -4121 is there as an explanation of the Excel constant "xlDown".

Whenever possible, it's actually best to use the Excel constants, as
they won't change across the various versions. Sadly, when using late
binding, that isn't possible.

On Sun, 25 Sep 2005 21:50:33 +1000, "Dixie" <di***@dogmail.com> wrote:
What does the line:
Selection.Insert Shift:=-4121 '-4121 = xlDown
mean Chuck? Obviously, it inserts a blank line, but it seems a bit
strange.

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:t3********************************@4ax.com. ..
Just be aware that for this to work, the active cell where you want
the insert to occur at must be the selected first. If someone goes in
and moves the active cell to something other then A1, then you'll get
the inserted rows at the wrong place.
It may be smarter to either select the Active Cell yourself first, or
just select the Rows where you want the inserted rows to be yourself.
Rows("1:5").Select
Selection.Insert Shift:=-4121 '-4121 = xlDown
You may also want to be sure that the correct worksheet is selected,
before doing any of this. On Sun, 25 Sep 2005 19:57:35 +1000, "Dixie" <di***@dogmail.com> wrote:
The code to add one line was simply
ActiveCell.EntireRow.Insert
I didn't have to worry about where I wanted to insert it as it was above
the
first line, which must be the default, because it worked.
I simply repeated the line 5 times to insert 5 blank lines - not very
sophisticated, but that probably sums me up. :-)

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

Nov 13 '05 #8

P: n/a

Sure, but you can create a tab delimited file straight from Access, so
I'm not really sure why you'd want to get Excel into the process.

In any event, the syntax is:

ActiveSheet.SaveAs "somefilename.txt", 20 ' 20 = xlTextWindows

Then remember to Quit with:

Application.Quit False

So that Excel won't prompt you to save the file as a Excel Workbook.

On Mon, 26 Sep 2005 09:27:45 +1000, "Dixie" <di***@dogmail.com> wrote:
One more thing I need to know. Is it possible to save the spreadsheet file
as a tab delimited text file from within Access after these operations have
been done? The end result of all this needs to be a tab delimted text file
with the same file name and obviously .txt extension. I could save it
manually, but it would be nice if it were possible to do it in vba. "Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:55********************************@4ax.com.. .
The "'-4121 = xlDown" part is a comment. It's just there to indicate
that -4121 is there as an explanation of the Excel constant "xlDown".
Whenever possible, it's actually best to use the Excel constants, as
they won't change across the various versions. Sadly, when using late
binding, that isn't possible. On Sun, 25 Sep 2005 21:50:33 +1000, "Dixie" <di***@dogmail.com> wrote:
What does the line:
Selection.Insert Shift:=-4121 '-4121 = xlDown
mean Chuck? Obviously, it inserts a blank line, but it seems a bit
strange. "Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:t3********************************@4ax.com ...
Just be aware that for this to work, the active cell where you want
the insert to occur at must be the selected first. If someone goes in
and moves the active cell to something other then A1, then you'll get
the inserted rows at the wrong place.
It may be smarter to either select the Active Cell yourself first, or
just select the Rows where you want the inserted rows to be yourself.
Rows("1:5").Select
Selection.Insert Shift:=-4121 '-4121 = xlDown
You may also want to be sure that the correct worksheet is selected,
before doing any of this. On Sun, 25 Sep 2005 19:57:35 +1000, "Dixie" <di***@dogmail.com> wrote:
>The code to add one line was simply
>ActiveCell.EntireRow.Insert
>I didn't have to worry about where I wanted to insert it as it was above
>the
>first line, which must be the default, because it worked.
>I simply repeated the line 5 times to insert 5 blank lines - not very
>sophisticated, but that probably sums me up. :-)

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

Nov 13 '05 #9

P: n/a
The line that saves the file is working,
ActiveSheet.SaveAs "somefilename.txt", 20 ' 20 = xlTextWindows
but the next line,
Application.Quit False
is closing the Access application down and leaving the Excel Application
open with the file Somefile.txt loaded into it.

Have I done something wrong here?

BTW, I am doing this through a spreadsheet as I am trying to emulate a
system that does it this way. It adds the extra lines in and adds some
values to certain of the cells in those five lines as well. This makes it
not possible for me to do it via Access - at least as far as I can see.

dixie

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:pl********************************@4ax.com...

Sure, but you can create a tab delimited file straight from Access, so
I'm not really sure why you'd want to get Excel into the process.

In any event, the syntax is:

ActiveSheet.SaveAs "somefilename.txt", 20 ' 20 = xlTextWindows

Then remember to Quit with:

Application.Quit False

So that Excel won't prompt you to save the file as a Excel Workbook.

On Mon, 26 Sep 2005 09:27:45 +1000, "Dixie" <di***@dogmail.com> wrote:
One more thing I need to know. Is it possible to save the spreadsheet
file
as a tab delimited text file from within Access after these operations
have
been done? The end result of all this needs to be a tab delimted text
file
with the same file name and obviously .txt extension. I could save it
manually, but it would be nice if it were possible to do it in vba.

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:55********************************@4ax.com. ..
The "'-4121 = xlDown" part is a comment. It's just there to indicate
that -4121 is there as an explanation of the Excel constant "xlDown".
Whenever possible, it's actually best to use the Excel constants, as
they won't change across the various versions. Sadly, when using late
binding, that isn't possible. On Sun, 25 Sep 2005 21:50:33 +1000, "Dixie" <di***@dogmail.com> wrote:
What does the line:
Selection.Insert Shift:=-4121 '-4121 = xlDown
mean Chuck? Obviously, it inserts a blank line, but it seems a bit
strange."Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:t3********************************@4ax.co m...
> Just be aware that for this to work, the active cell where you want
> the insert to occur at must be the selected first. If someone goes in
> and moves the active cell to something other then A1, then you'll get
> the inserted rows at the wrong place.
> It may be smarter to either select the Active Cell yourself first, or
> just select the Rows where you want the inserted rows to be yourself.
> Rows("1:5").Select
> Selection.Insert Shift:=-4121 '-4121 = xlDown
> You may also want to be sure that the correct worksheet is selected,
> before doing any of this. On Sun, 25 Sep 2005 19:57:35 +1000, "Dixie" <di***@dogmail.com> wrote:
>>The code to add one line was simply
>>ActiveCell.EntireRow.Insert
>>I didn't have to worry about where I wanted to insert it as it was
>>above
>>the
>>first line, which must be the default, because it worked.
>>I simply repeated the line 5 times to insert 5 blank lines - not very
>>sophisticated, but that probably sums me up. :-)

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

Nov 13 '05 #10

P: n/a
Excel is a disease.

KEEP YOUR DATA IN A DATABASE.
USE A REAL REPORTING PLATFORM.

Excel is a total waste of time

Nov 13 '05 #11

P: n/a

Without seeing what you're code _really_ looks like, it's a bit hard
to debug, but you _should_ be setting a object to Excel, and then
prefacing all the commands with that object reference. A *small*
example of this would be something like:

Set objExcel = CreateObject("excel.application")
objExcel.Workbooks.Open "C:\Test\myExcelFile.xls"

'Do whatever here

objExcel.ActiveWorkbook.SaveAs _
"C:\My Documents\myOtherExcelFile.xls"
objExcel.Application.Quit False
Set objExcel = Nothing

As for requiring Excel, from that brief bit you posted, it sounds like
something you could be doing all in Access. But again, without seeing
the code, it's hard to say for sure. Basically, if you can create a
query that have those values in it, then just UNION it to the query
you are exporting, then you should (in theory anyways) be done.

For Example, the query below will list the queries you have in your
database plus 5 rows that don't exist:

SELECT '' As ObjectType, 'BlankDummy' As [Name] FROM MSysObjects UNION
SELECT 'N2' As ObjectType, 'Dummy2' As [Name] FROM MSysObjects UNION
SELECT 'N3' As ObjectType, 'Dummy3' As [Name] FROM MSysObjects UNION
SELECT 'N4' As ObjectType, 'Dummy4' As [Name] FROM MSysObjects UNION
SELECT 'N5' As ObjectType, 'Dummy5' As [Name] FROM MSysObjects UNION
SELECT 'Query' AS ObjectType, [Name]
FROM MSysObjects
WHERE [Name] Not Like 'MSys*' And
[Name] Not Like 'Usys' AND
[Type] = 5 AND
Left$([Name],1)<>'~'
ORDER BY [ObjectType];

On Mon, 26 Sep 2005 20:19:17 +1000, "Dixie" <di***@dogmail.com> wrote:
The line that saves the file is working,
ActiveSheet.SaveAs "somefilename.txt", 20 ' 20 = xlTextWindows
but the next line,
Application.Quit False
is closing the Access application down and leaving the Excel Application
open with the file Somefile.txt loaded into it.
Have I done something wrong here?
BTW, I am doing this through a spreadsheet as I am trying to emulate a
system that does it this way. It adds the extra lines in and adds some
values to certain of the cells in those five lines as well. This makes it
not possible for me to do it via Access - at least as far as I can see. "Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:pl********************************@4ax.com.. .
Sure, but you can create a tab delimited file straight from Access, so
I'm not really sure why you'd want to get Excel into the process.
In any event, the syntax is:
ActiveSheet.SaveAs "somefilename.txt", 20 ' 20 = xlTextWindows
Then remember to Quit with:
Application.Quit False
So that Excel won't prompt you to save the file as a Excel Workbook. On Mon, 26 Sep 2005 09:27:45 +1000, "Dixie" <di***@dogmail.com> wrote:
One more thing I need to know. Is it possible to save the spreadsheet
file
as a tab delimited text file from within Access after these operations
have
been done? The end result of all this needs to be a tab delimted text
file
with the same file name and obviously .txt extension. I could save it
manually, but it would be nice if it were possible to do it in vba. "Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:55********************************@4ax.com ...
The "'-4121 = xlDown" part is a comment. It's just there to indicate
that -4121 is there as an explanation of the Excel constant "xlDown".
Whenever possible, it's actually best to use the Excel constants, as
they won't change across the various versions. Sadly, when using late
binding, that isn't possible.

On Sun, 25 Sep 2005 21:50:33 +1000, "Dixie" <di***@dogmail.com> wrote:
>What does the line:
>Selection.Insert Shift:=-4121 '-4121 = xlDown
>mean Chuck? Obviously, it inserts a blank line, but it seems a bit
>strange."Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
>news:t3********************************@4ax.c om...
>> Just be aware that for this to work, the active cell where you want
>> the insert to occur at must be the selected first. If someone goes in
>> and moves the active cell to something other then A1, then you'll get
>> the inserted rows at the wrong place.
>> It may be smarter to either select the Active Cell yourself first, or
>> just select the Rows where you want the inserted rows to be yourself.
>> Rows("1:5").Select
>> Selection.Insert Shift:=-4121 '-4121 = xlDown
>> You may also want to be sure that the correct worksheet is selected,
>> before doing any of this. On Sun, 25 Sep 2005 19:57:35 +1000, "Dixie" <di***@dogmail.com> wrote:
>>>The code to add one line was simply
>>>ActiveCell.EntireRow.Insert
>>>I didn't have to worry about where I wanted to insert it as it was
>>>above
>>>the
>>>first line, which must be the default, because it worked.
>>>I simply repeated the line 5 times to insert 5 blank lines - not very
>>>sophisticated, but that probably sums me up. :-)

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

Nov 13 '05 #12

P: n/a
I got around the problem I had with the following code.

xl.ActiveSheet.SaveAs CurrentProject.Path & "\Reports\somefilename.txt", 20

xl.Application.DisplayAlerts = False

xl.Application.Quit

xl.Application.DisplayAlerts = True

Set xl = Nothing

I just click the button on a form and it all happens and returns back to the
form without me having to intervene in the process. I think you are quite
right about the queries though. I will investigate this later, as what I
wanted to achieve is now working fine thanks to your help. It would be much
smoother to do it within Access entirely.

I have not see MySysObjects used in code before - I just thought it was an
internal Access table. Could you give a layperson's explanation of what
each of the SELECT lines actually does?

Thanks for your help.

dixie

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:1v********************************@4ax.com...

Without seeing what you're code _really_ looks like, it's a bit hard
to debug, but you _should_ be setting a object to Excel, and then
prefacing all the commands with that object reference. A *small*
example of this would be something like:

Set objExcel = CreateObject("excel.application")
objExcel.Workbooks.Open "C:\Test\myExcelFile.xls"

'Do whatever here

objExcel.ActiveWorkbook.SaveAs _
"C:\My Documents\myOtherExcelFile.xls"
objExcel.Application.Quit False
Set objExcel = Nothing

As for requiring Excel, from that brief bit you posted, it sounds like
something you could be doing all in Access. But again, without seeing
the code, it's hard to say for sure. Basically, if you can create a
query that have those values in it, then just UNION it to the query
you are exporting, then you should (in theory anyways) be done.

For Example, the query below will list the queries you have in your
database plus 5 rows that don't exist:

SELECT '' As ObjectType, 'BlankDummy' As [Name] FROM MSysObjects UNION
SELECT 'N2' As ObjectType, 'Dummy2' As [Name] FROM MSysObjects UNION
SELECT 'N3' As ObjectType, 'Dummy3' As [Name] FROM MSysObjects UNION
SELECT 'N4' As ObjectType, 'Dummy4' As [Name] FROM MSysObjects UNION
SELECT 'N5' As ObjectType, 'Dummy5' As [Name] FROM MSysObjects UNION
SELECT 'Query' AS ObjectType, [Name]
FROM MSysObjects
WHERE [Name] Not Like 'MSys*' And
[Name] Not Like 'Usys' AND
[Type] = 5 AND
Left$([Name],1)<>'~'
ORDER BY [ObjectType];

On Mon, 26 Sep 2005 20:19:17 +1000, "Dixie" <di***@dogmail.com> wrote:
The line that saves the file is working,
ActiveSheet.SaveAs "somefilename.txt", 20 ' 20 = xlTextWindows
but the next line,
Application.Quit False
is closing the Access application down and leaving the Excel Application
open with the file Somefile.txt loaded into it.
Have I done something wrong here?
BTW, I am doing this through a spreadsheet as I am trying to emulate a
system that does it this way. It adds the extra lines in and adds some
values to certain of the cells in those five lines as well. This makes it
not possible for me to do it via Access - at least as far as I can see.

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:pl********************************@4ax.com. ..
Sure, but you can create a tab delimited file straight from Access, so
I'm not really sure why you'd want to get Excel into the process.
In any event, the syntax is:
ActiveSheet.SaveAs "somefilename.txt", 20 ' 20 = xlTextWindows
Then remember to Quit with:
Application.Quit False
So that Excel won't prompt you to save the file as a Excel Workbook. On Mon, 26 Sep 2005 09:27:45 +1000, "Dixie" <di***@dogmail.com> wrote:
One more thing I need to know. Is it possible to save the spreadsheet
file
as a tab delimited text file from within Access after these operations
have
been done? The end result of all this needs to be a tab delimted text
file
with the same file name and obviously .txt extension. I could save it
manually, but it would be nice if it were possible to do it in vba."Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:55********************************@4ax.co m...
> The "'-4121 = xlDown" part is a comment. It's just there to indicate
> that -4121 is there as an explanation of the Excel constant "xlDown".
> Whenever possible, it's actually best to use the Excel constants, as
> they won't change across the various versions. Sadly, when using late
> binding, that isn't possible.

> On Sun, 25 Sep 2005 21:50:33 +1000, "Dixie" <di***@dogmail.com> wrote:
>>What does the line:
>>Selection.Insert Shift:=-4121 '-4121 = xlDown
>>mean Chuck? Obviously, it inserts a blank line, but it seems a bit
>>strange."Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
>>news:t3********************************@4ax. com...
>>> Just be aware that for this to work, the active cell where you want
>>> the insert to occur at must be the selected first. If someone goes
>>> in
>>> and moves the active cell to something other then A1, then you'll
>>> get
>>> the inserted rows at the wrong place.
>>> It may be smarter to either select the Active Cell yourself first,
>>> or
>>> just select the Rows where you want the inserted rows to be
>>> yourself.
>>> Rows("1:5").Select
>>> Selection.Insert Shift:=-4121 '-4121 = xlDown
>>> You may also want to be sure that the correct worksheet is selected,
>>> before doing any of this.> On Sun, 25 Sep 2005 19:57:35 +1000, "Dixie" <di***@dogmail.com>
>>> wrote:
>>>>The code to add one line was simply
>>>>ActiveCell.EntireRow.Insert
>>>>I didn't have to worry about where I wanted to insert it as it was
>>>>above
>>>>the
>>>>first line, which must be the default, because it worked.
>>>>I simply repeated the line 5 times to insert 5 blank lines - not
>>>>very
>>>>sophisticated, but that probably sums me up. :-)

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

Nov 13 '05 #13

P: n/a

Glad you have your problem with saving the file solved.

Basically, what the SQL query I posted does is to "select" 5 lines
that don't exist, as well as a list of the queries in your database.
The MsysObjects table is a internal table that Access uses to keep
track of what objects (tables, forms, queries, modules, reports, etc.)
you have in your database. This is a table that everyone has in every
database, so it's "handy" for illustrating things. You don't have
write permissions to the table, so it can be used for things like
showing how a query works with relative safety, and since it's in
every database everyone has, it's a table I know you have! <Big Grin>

Microsoft probably wouldn't agree with me on that, of course. No one
is really supposed to "pull the curtain aside and see the wizard", but
it's so blasted handy for building a list of the various objects in a
database, it's used fairly frequently for doing just that.

If you want to see some of the things that can be done with the
MsysObjects table, you might want to spend some time at
groups.google.com searching this newsgroup for "MsysObjects" and
you'll see some of our past recommendations for using it in "real
world" (and not so real world) applications. Personally, I use it
quite a bit for things like building lists of external databases for
re-linking when the database moves, or building a list of objects the
user can select to open.

As for the actual SQL itself, the first 5 lines are just select
statements. You'll note in those lines that I'm selecting against the
MsysObjects table, even though I'm not pulling any fields from the
table. This is a bit of an Access "strangeness" in that Access
requires a table (any table!) to pull against, even though you're not
pulling any fields from the table. In those first 5 lines, you can
replace the MsysObjects table with any table you want in all reality.
It doesn't matter since you're not really going to use the table,
Access just needs _a_ table name. (In other databases, the query
looks the same, it just doesn't have the FROM statements.)

The last lines are a fairly normal select that uses the MsysObjects
table. See above for why I choose that table to illustrate the query.
The WHERE statement in the last query eliminates the "hidden" queries,
as well as the deleted and temporary SQL statements that Access uses
for things like drop downs and list boxes.

The only other thing left is the UNION statement. The SQL UNION
statement is a device for joining several queries together into 1 big
query. Access' query editor isn't able to graphically show these (and
to be honest, I can't imagine what one would look like myself, so you
can only write and work with these in the editor's SQL mode. (Just go
to View -> SQL View to get to that.)

SQL View is one that I strongly suggest that anyone who is seriously
getting into Access use. You don't have to use it for writing queries
(heaven knows, I certainly don't unless I have to!), but it shows
_how_ Access is using SQL to do it's work. You'll also see from time
to time, how it can write a SQL query badly. (But to recognize that,
you'll have to learn a lot of SQL.)

On Tue, 27 Sep 2005 09:50:09 +1000, "Dixie" <di***@dogmail.com> wrote:
I have not see MySysObjects used in code before - I just thought it was an
internal Access table. Could you give a layperson's explanation of what
each of the SELECT lines actually does? "Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:1v********************************@4ax.com.. .
As for requiring Excel, from that brief bit you posted, it sounds like
something you could be doing all in Access. But again, without seeing
the code, it's hard to say for sure. Basically, if you can create a
query that have those values in it, then just UNION it to the query
you are exporting, then you should (in theory anyways) be done.
For Example, the query below will list the queries you have in your
database plus 5 rows that don't exist:
SELECT '' As ObjectType, 'BlankDummy' As [Name] FROM MSysObjects UNION
SELECT 'N2' As ObjectType, 'Dummy2' As [Name] FROM MSysObjects UNION
SELECT 'N3' As ObjectType, 'Dummy3' As [Name] FROM MSysObjects UNION
SELECT 'N4' As ObjectType, 'Dummy4' As [Name] FROM MSysObjects UNION
SELECT 'N5' As ObjectType, 'Dummy5' As [Name] FROM MSysObjects UNION
SELECT 'Query' AS ObjectType, [Name]
FROM MSysObjects
WHERE [Name] Not Like 'MSys*' And
[Name] Not Like 'Usys' AND
[Type] = 5 AND
Left$([Name],1)<>'~'
ORDER BY [ObjectType]; On Mon, 26 Sep 2005 20:19:17 +1000, "Dixie" <di***@dogmail.com> wrote:
The line that saves the file is working,
ActiveSheet.SaveAs "somefilename.txt", 20 ' 20 = xlTextWindows
but the next line,
Application.Quit False
is closing the Access application down and leaving the Excel Application
open with the file Somefile.txt loaded into it.
Have I done something wrong here?
BTW, I am doing this through a spreadsheet as I am trying to emulate a
system that does it this way. It adds the extra lines in and adds some
values to certain of the cells in those five lines as well. This makes it
not possible for me to do it via Access - at least as far as I can see. "Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:pl********************************@4ax.com ...
Sure, but you can create a tab delimited file straight from Access, so
I'm not really sure why you'd want to get Excel into the process.
In any event, the syntax is:
ActiveSheet.SaveAs "somefilename.txt", 20 ' 20 = xlTextWindows
Then remember to Quit with:
Application.Quit False
So that Excel won't prompt you to save the file as a Excel Workbook. On Mon, 26 Sep 2005 09:27:45 +1000, "Dixie" <di***@dogmail.com> wrote:
>One more thing I need to know. Is it possible to save the spreadsheet
>file
>as a tab delimited text file from within Access after these operations
>have
>been done? The end result of all this needs to be a tab delimted text
>file
>with the same file name and obviously .txt extension. I could save it
>manually, but it would be nice if it were possible to do it in vba."Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
>news:55********************************@4ax.c om...
>> The "'-4121 = xlDown" part is a comment. It's just there to indicate
>> that -4121 is there as an explanation of the Excel constant "xlDown".
>> Whenever possible, it's actually best to use the Excel constants, as
>> they won't change across the various versions. Sadly, when using late
>> binding, that isn't possible.

>> On Sun, 25 Sep 2005 21:50:33 +1000, "Dixie" <di***@dogmail.com> wrote:
>>>What does the line:
>>>Selection.Insert Shift:=-4121 '-4121 = xlDown
>>>mean Chuck? Obviously, it inserts a blank line, but it seems a bit
>>>strange."Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
>>>news:t3********************************@4ax .com...
>>>> Just be aware that for this to work, the active cell where you want
>>>> the insert to occur at must be the selected first. If someone goes
>>>> in
>>>> and moves the active cell to something other then A1, then you'll
>>>> get
>>>> the inserted rows at the wrong place.
>>>> It may be smarter to either select the Active Cell yourself first,
>>>> or
>>>> just select the Rows where you want the inserted rows to be
>>>> yourself.
>>>> Rows("1:5").Select
>>>> Selection.Insert Shift:=-4121 '-4121 = xlDown
>>>> You may also want to be sure that the correct worksheet is selected,
>>>> before doing any of this.> On Sun, 25 Sep 2005 19:57:35 +1000, "Dixie" <di***@dogmail.com>
>>>> wrote:
>>>>>The code to add one line was simply
>>>>>ActiveCell.EntireRow.Insert
>>>>>I didn't have to worry about where I wanted to insert it as it was
>>>>>above
>>>>>the
>>>>>first line, which must be the default, because it worked.
>>>>>I simply repeated the line 5 times to insert 5 blank lines - not
>>>>>very
>>>>>sophisticated, but that probably sums me up. :-)

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

Nov 13 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.