Pass formula to Excel spreadsheet

Actually Tom, Outlook Express is keeping these messages together on my PC as
I have the option "Group Messages by Conversation" selected under
"ViewCurrent View", though this may not apply to other newsreaders.
However, as this is effectively a different thread.....

--
Ian
--
"Tom Wickerath" AOS168b AT comcast DOT net wrote in message
...
Hi Daniel,

It is not a good idea to change the subject when posting a reply. While
the
Web Portal maintains your post within the same thread, other newsreader
software, such as Outlook Express, will not. Thus, the responses that you
received from two other people indicating that they didn't know what you
were
talking about. For the benefit of those who are not using the web portal,
here is a link to the thread in question:

http://www.microsoft.com/office/comm...1-786145e0c3e6

To answer your question, you can use either DoCmd.TransferSpreadsheet or
DoCmd.OutputTo to export your data to Excel. There are some differences in
how these methods work. DoCmd.TransferSpreadsheet does NOT replace an
existing spreadsheet of the same name. It can export 65536 records maximum
(Excel 2000, 2002 or 2003). DoCmd.OutputTo is limited to around 16K
records.

For example:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
strSourceName, strFileName, True

strSourceName is the source of your data (ie. your query). strFileName is
the name of the Excel file. If you run this command several times, varying
the data source, you will create separate worksheets within the same (1)
spreadsheet file.

DoCmd.OutputTo replaces an existing spreadsheet of the same name. For
example:

DoCmd.OutputTo acOutputQuery, strSourceName, acFormatXLS, _
strFileName, AutoStart:=0

Here is an example of running the function shown below from the immediate
window:

?ExportToExcel("qryMaintInfo", "Maintenance Information.xls")

Use either DoCmd.TransferSpreadsheet or DoCmd.OutputTo, but not both. You
can call this function from another procedure, where you pass in the names
of
each query that you need to export, one by one.


Option Compare Database
Option Explicit

Function ExportToExcel(strSourceName As String, strFileName As String)
On Error GoTo ProcError

strFileName = CurrentProject.Path & "\" & strFileName

' The following command does NOT replace an existing spreadsheet of
' the same name
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
strSourceName, strFileName, True

' The following command replaces an existing spreadsheet of the same
name
'DoCmd.OutputTo acOutputQuery, strSourceName, acFormatXLS, _
strFileName, AutoStart:=0


ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure ExportToExcel..."
Resume ExitProc
End Function


Here is another example, using the click event of a command button, where
a
check is made to verify that the user is not attempting to export too many
records. In this case, the records to be exported are displayed in a
subform.

Note: Excel 2007 can accomodate a lot more records, but I'm not sure, at
this time, if DoCmd.Transferspreadsheet has been updated to handle this
increased capacity (or if it even needs to be updated).

Private Sub cmdExportToExcel_Click()
On Error GoTo ProcError

Dim strPath As String
Dim lngRecordCount As Long

lngRecordCount = Me.subQueryByForm.Form.Recordset.RecordCount
strPath = CurrentProject.Path

If lngRecordCount 65536 Then
MsgBox "There are too many records to export." & vbCrLf _
& "The maximum limit is 65,536 records.", _
vbCritical, "Too Many Records..."
Else
DoCmd.TransferSpreadsheet TransferType:=acExport, _
TableName:="qryQBF", _
FileName:=strPath & "\UNITDATA.xls", HasFieldNames:=True

MsgBox "The selected data has been exported to the file UNITDATA.xls" _
& vbCrLf & "in the folder:" & vbCrLf & _
strPath, vbInformation, "Export Complete..."
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in cmdExportToExcel_Click event procedure..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


"Daniel Peel" wrote:

I want to do the same thing, however, what I'm tring to do it have a
database pull a query and fill in information on an excel spreadsheet,
with the addition that there may be 20 or 30 different recordsets. I
need a new spreadsheet for each record.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Ian,

Check out this link, if you'd like to see what I am seeing when I use
Outlook Express with messages grouped:

http://home.comcast.net/~tutorme2/images/group.gif


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Ian" wrote:

Actually Tom, Outlook Express is keeping these messages together on my PC as
I have the option "Group Messages by Conversation" selected under
"ViewCurrent View", though this may not apply to other newsreaders.
However, as this is effectively a different thread.....

--
Ian

Hi Tom

I can't argue with the gif image, but with the same settings the
conversation flows from one subject to the next. The only difference I can
see is that I have the conversation marked as watched. Perhaps that makes
the difference.

--
Ian
--
"Tom Wickerath" AOS168b AT comcast DOT net wrote in message
...
Ian,

Check out this link, if you'd like to see what I am seeing when I use
Outlook Express with messages grouped:

http://home.comcast.net/~tutorme2/images/group.gif


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Ian" wrote:

Actually Tom, Outlook Express is keeping these messages together on my PC
as
I have the option "Group Messages by Conversation" selected under
"ViewCurrent View", though this may not apply to other newsreaders.
However, as this is effectively a different thread.....

--
Ian

I don't know if marking a conversation is what makes the difference in your
case...
I usually use the web portal myself.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Ian" wrote:

Hi Tom

I can't argue with the gif image, but with the same settings the
conversation flows from one subject to the next. The only difference I can
see is that I have the conversation marked as watched. Perhaps that makes
the difference.

--
Ian

Outlook Express does continue to thread everything together even if the
subject is changed. However, if you tell OE to Show All Message, all you'll
see is the original thread name as the root of the thread.

Once the thread diverges, though, if you have OE set to Hide Read Messages,
then they'll show up in your reader as multiple threads.

The problem here was that the divergence happened on January 9th to a thread
that was started on December 29th. I marked the "Question along the same
lines" post as unread once I'd marked everything else as read, changed the
view to Show All Messages and tried to find the original thread ("Pass
formula to Excel spreadsheet"). I obviously didn't scroll back far enough: a
week and a half in this newsgroup is a lot of messages! (I did scroll back
far enough this time, and now have a screen shot showing both subjects in
the same thread if you're interested, Tom)

I wonder whether Daniel ever reposted to get his tag-along question answered
or not... g

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Tom Wickerath" AOS168b AT comcast DOT net wrote in message
...
I don't know if marking a conversation is what makes the difference in your
case...
I usually use the web portal myself.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Ian" wrote:

Hi Tom

I can't argue with the gif image, but with the same settings the
conversation flows from one subject to the next. The only difference I
can
see is that I have the conversation marked as watched. Perhaps that makes
the difference.

--
Ian

Tom,

Sorry about the subject change, however, i'm not using outlook express and using a website called eggheadcafe.com and it doesn't copy over the subject when posting a reply. I also didn't see the two replies that asked what I was talking about.

Thanks for the answer I'll see how I can make that work. I'll go into a bit more detail so maybe you'll understand. I have a excel sheet that we print out and then fill in by hand. Only 4 fields need to be filled in. Name, Number, Phone, and fax and the total number at any one time won't be more than 50 or so. So my recordset will be small. I hoping keep that file the same and have it simply spit out the 4 fields to the spreadsheet print it and then do the same time over again until all are finished. It doesn't have to be in that order. right now i've created an image of the spreadsheet and made it fit into a report and placed the fields on that image. this is working, however, the acutal spreadsheet is larger and excel does a better job of shrinking it to fit one page vs making it smaller in an image editing program. Plus if I need to redo the spreadsheet in the future having it use the acutal file is much simpler to edit later rather than coming home redoing the mde and taking it back to work as I can do the excel editing at work. Access 2003 is what is being used. If you need anything else let me know and thanks again for the answer.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Hi Daniel,

I have a excel sheet that we print out and then fill in by hand.

Fill what out by hand?


Only 4 fields need to be filled in. Name, Number, Phone, and fax and the
total number at any one time won't be more than 50 or so. So my recordset
will be small.

I assume you are referring to the total number or rows (records) in the
Excel spreadsheet. Is this correct? Presumably the data is exported from
your Access application. In any case, this doesn't sound like a problem.


I hoping keep that file the same and have it simply spit out the 4 fields to the
spreadsheet print it and then do the same time over again until all are finished.

Ummm....is there any reason that you cannot export all records in one
operation?


right now i've created an image of the spreadsheet and made it fit into a
report and placed the fields on that image.

Why are you doing this image processing? That sounds like a LOT of extra
work. Can you not simply create a nice formatted report in Access and print
it directly?



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Daniel Peel" wrote:

Tom,

Sorry about the subject change, however, i'm not using outlook express and using a website called eggheadcafe.com and it doesn't copy over the subject when posting a reply. I also didn't see the two replies that asked what I was talking about.

Thanks for the answer I'll see how I can make that work. I'll go into a bit more detail so maybe you'll understand. I have a excel sheet that we print out and then fill in by hand. Only 4 fields need to be filled in. Name, Number, Phone, and fax and the total number at any one time won't be more than 50 or so. So my recordset will be small. I hoping keep that file the same and have it simply spit out the 4 fields to the spreadsheet print it and then do the same time over again until all are finished. It doesn't have to be in that order. right now i've created an image of the spreadsheet and made it fit into a report and placed the fields on that image. this is working, however, the acutal spreadsheet is larger and excel does a better job of shrinking it to fit one page vs making it smaller in an image editing program. Plus if I need to redo the spreadsheet in the future having it use the acutal file is much simpler to edit later rather than coming home redoing the mde
and taking it back to work as I can do the excel editing at work. Access 2003 is what is being used. If you need anything else let me know and thanks again for the answer.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

On Fri, 12 Jan 2007 23:03:50 -0800, Daniel Peel wrote:

Thanks for the answer I'll see how I can make that work. I'll go into a bit more detail so maybe you'll understand. I have a excel sheet that we print out and then fill in by hand. Only 4 fields need to be filled in. Name, Number, Phone, and fax and the total number at any one time won't be more than 50 or so. So my recordset will be small. I hoping keep that file the same and have it simply spit out the 4 fields to the spreadsheet print it and then do the same time over again until all are finished.

If you're trying to get records from Access to a sheet of paper, why
involve Excel AT ALL!?

You can very easily create a Report based on the query, and print
*THAT* sheet of paper. Access is actually *better* than Excel at
formatting reports.

John W. Vinson[MVP]