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
13  7738 
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
Dixie,
That sounds very useful. Could you share it with us?
Hank
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
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
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
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
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
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
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
Excel is a disease.
KEEP YOUR DATA IN A DATABASE.
USE A REAL REPORTING PLATFORM.
Excel is a total waste of time
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: smonczka |
last post by:
I have a DTS package that needs to refresh data in 3 separate Excel
spreadsheets on a daily basis. The problem is that unless I manually
delete the previous day's data, it appends rather than...
|
by: Elaksomfan |
last post by:
Hi,
I am having trouble importing data from an excel spreadsheet into MS
SQL Server 2000 using DTS Wizard. The DTS import process is
successfull, no errors, but only 50 rows of approx. 1500 rows...
|
by: Anne Sachleben via AccessMonster.com |
last post by:
I am using the TransferSpreadsheet function to export a query result to a
specific worksheet in an Excel file titled "report". I want the result to
be exported to the worksheet titled "facts". ...
|
by: sysmanint1 |
last post by:
I am a total neophyte at Visual Basic but found the following post and reply from Clint concerning a dynamic range.
Also, have never "posted" to a discussion
I have made a macro that works on...
|
by: davnao |
last post by:
I would like to open an existing workbook, select a work sheet and insert multiple rows at the top of the spreadsheet. Have the following code, but unsure of proper command & syntax for Insert row,...
|
by: GiftX |
last post by:
Hello members,
I need help with my spreadsheet display problem. I generated my spreadsheet in using vb6.
Everything is working except that when I display the display (making it visible), some of...
|
by: sitko |
last post by:
Hi,
I have an Order tracking spreadsheet that I need help with.
I have a 2 worksheets "Open", and "Closed".
I have entries on the "Open" sheet which may or may not be grouped together. I've...
|
by: Naldo |
last post by:
Hi there
I'm trying to make a spreadsheet, which reads other spreadsheets and displays info accordingly.
I'm stuck though. I read off one spreadsheet where I find the col required (row 1) then...
|
by: damiencarr |
last post by:
i have a spreadsheet/worksheet called "NTH_Alliance" containing rows of related data grouped together under different headings, that are named as different weeks (e.g. Week 5 (26/01/2009 -...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
