exporting a querry to a preformatted excel workbook

[Guest]

I am building an access database to simplify data entry into mulitple excel
sheets. Some of the information is being saved in tables and viewed with
reports, but since access is not widely used, I need to export a querry to an
excel database. Simple in theory. However, I am having troulbe with the
TransferSpreadsheet function.

I need to export the data into an established, formatted worksheet, not to
just create a new worksheet in my workbook.

I am currently using a very customized worksheet- the first 6 rows are
frozen and serve as the header/colum titles and most of the columns have
conditional formatting. I am trying to export the access data to this
worksheet so that I do not have to manipulate/format the data before
emailing. Any help would be greatly appreciated. Thanks!

 

[Van T. Dinh]

I don't think you can use TransferSpreadsheet in this case.

One work-around is to create a Recordset and then use Excel Automation and
the CopyFromRecordset Method in the automated Excel object.

Check Excel VBA Help on the CopyFromRecordset Method.

 

[Ken Snell [MVP]]

Actually, you can use TransferSpreadsheet for this. You specify the
worksheet name in the Range argument of this method.

Syntax for the worksheet name in that argument depends upon whether the
worksheet name contains a space or not. If it does not contain a space:
"WorksheetName!"

If it contains a space, delimit it with ' characters:

"'Worksheet Name'!"

 

[Van T. Dinh]

Have you tested this, Ken?

Here the relevant excerpt from Access (2002) Help on TransferSpreadsheet:

====
Range Optional Variant. A string expression that's a valid range of cells
or the name of a range in the spreadsheet. This argument applies only to
importing. Leave this argument blank to import the entire spreadsheet.
***When you export to a spreadsheet, you must leave this argument blank. If
you enter a range, the export will fail.***
====

 

[Ken Snell [MVP]]

I haven't personally tested it, but others who's comments I trust have
reported that it appears to be an undocumented feature that works (for how
long? who knows).

I also forgot to mention that there is another way to use
TransferSpreadsheet to do what the poster wants. If the workbook file
already contains a worksheet with the name of the query/table being exported
(or the first31 characters of that query/table name if it's longer than 31
characters), TransferSpeadsheet will write to that sheet and not create a
new sheet. No need to use the Range agument in this case.
--

Ken Snell
<MS ACCESS MVP>

 

[Van T. Dinh]

Ken

I did a quick test in A2002 and the export (with TransferSpreadsheet)
accepts the Named Range (contiguous, at least).

 

[Guest]

Thank you both for the input.
I am however, not having any luck with the TransferSpreadsheet macro.

I tried using the range section to specify the sheet in my excel workbook,
however it returned the error: "'Worksheet Name'$" is not a valid name. The
macro then proceeded to fail.

When i was first putting the macro together, it must have run and did append
the data to the end of the file, but it was not formatted with the current
excel formats. Im not sure when the macro ran, but after deleting those
results and rerunning the macro, I cannot get it to work again... very
stratnge.

I tried making the sheets one word instead of two, but that didnt change the
results, the macro still failed.

Not being able to resolve this problem, I renamed the querry (access data
source) to the same name as the excel worksheet (as Ken had suggested),
however, instead of appending the data to the existing "worksheet", excel
still created a new worksheet and named it "worksheet_1" and inserted the
data into this new worksheet.

If this helps, I am currently using Office 2003. Any ideas on getting
around this?

I tried to read up on the Recordset with Excel Automation that was suggested
earlier, but am a little confused on how to code this and I cannot find much
help in the Access Help files on these.

Again, any additional help/insight would be greatly appreciated.

~Vince

 

[Ken Snell [MVP]]

You need to post the exact names and data that you're using here.... that
may point to what is going on. Is the worksheet onto which the data are to
be exported locked?

 

[Guest]

I did play with the names last night... names are ok. Will doubt check the
data today- but should be ok. sheets are unlocked and unprotected right now.

This morning, I tried deleting all of the puntiuation, and just ran Block
Mix! in the Range box. It got rid of the naming error that I got last night,
however, I am now returning an error "Cannot delete spreadsheet cells".
Also, it seems by default in an export, the headings are always exported to
the first row. Is there anyway to over ride this (the first 7 rows of the
excel spreadsheet are header, I want to start the export in at least row 7,
preferably B7, but i can work around that if need be.

~Vince

 

[Ken Snell [MVP]]

I did play with the names last night... names are ok. Will doubt check the
data today- but should be ok. sheets are unlocked and unprotected right
now.

This morning, I tried deleting all of the puntiuation, and just ran Block
Mix! in the Range box. It got rid of the naming error that I got last
night,
however, I am now returning an error "Cannot delete spreadsheet cells".
Also, it seems by default in an export, the headings are always exported
to
the first row. Is there anyway to over ride this (the first 7 rows of the
excel spreadsheet are header, I want to start the export in at least row
7,
preferably B7, but i can work around that if need be.

TransferSpreadsheet will write header data to the first row only. If you
want to start in row 7, you'll need to use Automation in order to fully
manipulate the data the way you wish. Automation allows you to open the
EXCEL file, write your data into it where you want, and then close the EXCEL
file.

 

[Guest]

Also, if I (for arguments sake) delete all of the data, headers (the first 6
rows), and just leave cells in far right columns (beyond what would be
exported) The macro still fails saying Cannot delete spreadsheet cells

If I delete everything out of the excel sheet, The macro fails saying, Table
'Block Mix$' already exists. it just doesnt want to cooperate =)
~Vince

 

[Guest]

Ken,
I am not familiar with Automation. Searching in Access Help, so far has not
returned any usable help. Where can I find more information on this?

 

[Ken Snell [MVP]]

Automation is using VBA code to manipulate other OFFICE applications.

Some sample code that may get you started (you can add loops to this code to
loop through each worksheet in a book if you'd like). This code opens an
EXCEL workbook and reads data from a recordset and writes the data into the
worksheet. You can modify this as needed to do things directly on the
spreadsheet, etc.


Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("C:\Filename.xls"), , True
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("QueryName", dbOpenDynaset)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
' write header cells
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
Next lngColumn
Set xlc = xlc.Offset(1,0)
Do While rst.EOF = False
' write data cells
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Value
Next lngColumn
rst.MoveNext
Set xlc = xlc.Offset(1,0)
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing

 

[Guest]

Thanks for all the help exporting the data to excel! I started with the
sample code, found a couple access books (not sure how old- but it kept
things interesting) =) in any case- I finally have the code running! I
included some options for sorting and it exports from four querys into 4
worksheets keeping all of the original formatting. just as i hoped! of
course I sitll have alot more work to do, but this is definitly a benchmark.
thanks again!

 

[Ken Snell [MVP]]

Congratulations!