Export using transferspreadsheet

[dtroupe]

I am trying to automate the export of a very large
database that is allready importing automated from a text
file. The data only has 4 columns and right now I am
running a query with date range and exporting chunks to
text files then deleting the data. If I use the
transferspreadsheet option in a macro it doesn't add a new
sheet to the Excel2000 workbook, it just replaces the
file. Help says it is supposed to add a sheet to an
existing file if workbook is Excel2000?

 

[Ken Snell]

The behavior that you describe is not normal for the TransferSpreadsheet
action. Its normal action is the same as described in the Help file.

Please give us more info about all that you're doing here.

 

[dtroupe]

I have setup on my local w.s. Win200 PRO sp4, I have a
database in Access and a query for a date range in the
database. I built the small macro (transferSpreadsheet)
and when I pick the Run icon it created the Excel file on
my D:\ called export.xls. I am the administrator and am
logged in as such. I open the Excel file and there is a
sheet there with the name of the query and my data range.
I closed the file and run the macro again and checked the
file attributes and it changed the time stamp but when I
open the Excel file there is still only one tab. I changed
the date range for different data and run it again and it
changed the existing data but no new tab. I created
several spare sheets and saved the file and run it again
and still onlyt get one sheet with data. It is just
replacing the file each time. If I go in and change the
name of the sheet then a new sheet is created with the
query name and then I have two sheets.

 

[Ken Snell]

I did some quick testing of your issue. It appears that ACCESS will not
create a new spreadsheet if the exported query name is the same as a sheet
already in the EXCEL file. If so, it overwrites the data on that sheet with
the new export.

You'll need to change the name of the query that you're exporting for each
export in order to avoid this problem.

 

[John Thow]

I did some quick testing of your issue. It appears that ACCESS will not
create a new spreadsheet if the exported query name is the same as a sheet
already in the EXCEL file. If so, it overwrites the data on that sheet with
the new export.

You'll need to change the name of the query that you're exporting for each
export in order to avoid this problem.

[Snip]

An alternative to repeatedly renaming the query is to have a macro in the
Excel workbook that copies the data output by Access from the worksheet where
the Access query puts it to a worksheet with a name of your choice. The new
worksheet can be named either by parameter or have a name based on some
criterion of your choice. (Eg the date range of the data.)

HTH
--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[dtroupe]

I guess I need more macr training in Excel? What I could
do if anyone know how would be to have the macro auto
create this file and a auto run batch file rename this
file to the machine date and then another macro in Access
be able to delete the range that was just exported? My
problem is if I don't manually daily run this then after a
weekend I get more data than just one day. For example I
get 13248 lines of data per day. I then export to text
files for archiving and delete the data from the database.
The text files are then used in a variety of uses
primarily Excel. If I do not delete the data from Access
once exported you can imagine the size of the database in
a weeks time? I am auto importing right now and am looking
for a way to auto export and delete the data once exported.

-----Original Message-----

I did some quick testing of your issue. It appears that ACCESS will not
create a new spreadsheet if the exported query name is the same as a sheet
already in the EXCEL file. If so, it overwrites the data on that sheet with
the new export.

You'll need to change the name of the query that you're exporting for each
export in order to avoid this problem.

[Snip]

An alternative to repeatedly renaming the query is to have a macro in the
Excel workbook that copies the data output by Access from the worksheet where
the Access query puts it to a worksheet with a name of your choice. The new
worksheet can be named either by parameter or have a name based on some
criterion of your choice. (Eg the date range of the data.)

HTH
--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!
.

 

[Jim/Chris]

Why not link the spreadsheet and create a tempory table
combining the data in the spreadsheet and the new data.
Then do transfer spreadsheet in the macro

Jim

 

[dtroupe]

Maybe you could elaborate on this a little? I can create
the Excel file with the macro but how do I automate the
renaming of the sheet name to &&)&*_)*?? so the next
export will create the new sheet and be able to do this
unattended on a weekend?

 

[Jim/Chris]

I guess your concern is to have a different sheet for each
time you run this query

As you know Access uses the query name for the sheet name.
So if you want to keep a seperate sheet for each query run
why not copy your query and name it "todaysdate plus
queryname. That way the new excel sheet will be named
"120503QName" resulting in a new sheet for every day. Try
this:

In your macro
Action CopyObject
Destination Database Blank
New name =Format(Date(),"mmddyy") & "QName"
Source Object QName

Now you can add your transferspreadsheet action
Remember the name of the query you are transfer is
=Format(Date(),"mmddyy") & "QName"

You can then run something like "Windows task scheduler" to
automate this macro.

Oh don't forget to add a DeleteObject at the end of your
macro or you will end up with alot of queries

I hope this helped

Jim

 

[John Thow]

I guess I need more macr training in Excel? What I could
do if anyone know how would be to have the macro auto
create this file and a auto run batch file rename this
file to the machine date and then another macro in Access
be able to delete the range that was just exported? My
problem is if I don't manually daily run this then after a
weekend I get more data than just one day. For example I
get 13248 lines of data per day. I then export to text
files for archiving and delete the data from the database.
The text files are then used in a variety of uses
primarily Excel. If I do not delete the data from Access
once exported you can imagine the size of the database in
a weeks time? I am auto importing right now and am looking
for a way to auto export and delete the data once exported.

[Snip]

OK. I can help with the Excel macro, but there are folks around here much
better at Access ones. ;-)

Excel macro follows.

Sub Macro1()
'
' This macro inserts a new worksheet, names it with the system date & time and
' copies data from a worksheet output with a "standard" name by MS Access

Dim AccessSheetName As String
Dim DateStr As String
Dim NewSheetName As String

AccessSheetName = "<Whatever it's called>"
DateStr = Str(Now)
'Puts current date & time into a string

' The next 3 lines turn the date & time from "DD/MM/YYYY HH:MM:SS" to '
' "YYMMDD_HHmmSS"
NewSheetName = Mid(DateStr, 9, 2) & Mid(DateStr, 4, 2) & Left(DateStr, 2)
NewSheetName = NewSheetName & "_" & Mid(DateStr, 12, 2)
NewSheetName = NewSheetName & Mid(DateStr, 15, 2)& Right(DateStr, 2)

Sheets.Add
'Adds a new worksheet at the front of the workbook

ActiveSheet.Name = NewSheetName
'Renames the new sheet to date & time as above

'This bit copies the Access output sheet
Worksheets(AccessSheetName).Activate
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select

'This bit pastes the data to the new sheet
Worksheets(NewSheetName).Activate
Cells.Select
ActiveSheet.Paste
Range("A1").Select

End Sub

(You'll need to replace "<Whatever it's called>" with the real name - in
quotes but without the "<" & ">")

Hope this is some help towards your desired end.

--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Ken Snell]

It's entirely feasible to program ACCESS to handle all the data transfers
via VBA code (whether using EXCEL or text files), and then you wouldn't be
dependent on this behavior of TransferSpreadsheet. From the sounds of your
needs, the time spent on learning and implementing that approach would be
worthwhile.

At 13,248 lines of data a day, it would take about one year before you'd see
a significant size to your database. ACCESS 2000 and higher versions handle
databases up to 2 GB in size. And, you can archive data in other
databases -- the data don't need to stay in just one database.


--
Ken Snell
<MS ACCESS MVP>

I guess I need more macr training in Excel? What I could
do if anyone know how would be to have the macro auto
create this file and a auto run batch file rename this
file to the machine date and then another macro in Access
be able to delete the range that was just exported? My
problem is if I don't manually daily run this then after a
weekend I get more data than just one day. For example I
get 13248 lines of data per day. I then export to text
files for archiving and delete the data from the database.
The text files are then used in a variety of uses
primarily Excel. If I do not delete the data from Access
once exported you can imagine the size of the database in
a weeks time? I am auto importing right now and am looking
for a way to auto export and delete the data once exported.

-----Original Message-----

I did some quick testing of your issue. It appears that ACCESS will not
create a new spreadsheet if the exported query name is the same as a sheet
already in the EXCEL file. If so, it overwrites the data on that sheet with
the new export.

You'll need to change the name of the query that you're exporting for each
export in order to avoid this problem.

--
Ken Snell
<MS ACCESS MVP>
I have setup on my local w.s. Win200 PRO sp4, I have a
database in Access and a query for a date range in the
database. I built the small macro (transferSpreadsheet)
and when I pick the Run icon it created the Excel file on
my D:\ called export.xls. I am the administrator and am
logged in as such. I open the Excel file and there is a
sheet there with the name of the query and my data range.
I closed the file and run the macro again and checked the
file attributes and it changed the time stamp but when I
open the Excel file there is still only one tab. I changed
the date range for different data and run it again and it
changed the existing data but no new tab. I created
several spare sheets and saved the file and run it again
and still onlyt get one sheet with data. It is just
replacing the file each time. If I go in and change the
name of the sheet then a new sheet is created with the
query name and then I have two sheets.

[Snip]

An alternative to repeatedly renaming the query is to have a macro in the
Excel workbook that copies the data output by Access from the worksheet where
the Access query puts it to a worksheet with a name of your choice. The new
worksheet can be named either by parameter or have a name based on some
criterion of your choice. (Eg the date range of the data.)

HTH
--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!
.

 

[dtroupe]

Works fantastic, thanks for your help. Only one small
problem. In your advise to DeleteObject you didn't declare
that I just needed to leave the bottom 2 argument fields
blank. I tried putting something in there and it bombed on
me until I just deleted the row then added it back in and
didn't put anything in those fields. Then it really worked
good. Again thanks alot!