Access to Excel

[chad.wright]

I Have a form that has a button that when clicked it sends data from a
query that i have to a excel spreadsheet. the problem that i am having
is when i run it on a daily basis it over rides the previous days data.
is there a way around this. here is my code that i am using in access
vb.

Function SD_exportToExcel()

DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tablename:="qrySd_Sales", _
filename:="c:\my documents\sd_dailySales.xls", _
hasfieldnames:=True

End Function

thanks in advance.

 

[MGFoster]

I Have a form that has a button that when clicked it sends data from a
query that i have to a excel spreadsheet. the problem that i am having
is when i run it on a daily basis it over rides the previous days data.
is there a way around this. here is my code that i am using in access
vb.

Function SD_exportToExcel()

DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tablename:="qrySd_Sales", _
filename:="c:\my documents\sd_dailySales.xls", _
hasfieldnames:=True

End Function

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Change the name of the XLS file - add the date to the file name:

filename:="c:\my documents\sd_dailySales" & Date() & ".xls", _
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlciPIechKqOuFEgEQKTsgCgk+rkFRWu0IA7J1QZwzsSntshdigAoPjt
ZSesxF5mW8n4/428CeqkYboC
=rrO8
-----END PGP SIGNATURE-----

 

[chad.wright]

now will that just create a new excel file or just append to the old
one?

 

[chad.wright]

i get a runtime error '3044'
here is my code:

Function SD_exportToExcel()

DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tablename:="qrySd_Sales", _
filename:="c:\my documents\sd_dailySales" & Date & ".xls", _
hasfieldnames:=True

End Function

thanks
chad

 

[MGFoster]

i get a runtime error '3044'
here is my code:

Function SD_exportToExcel()

DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tablename:="qrySd_Sales", _
filename:="c:\my documents\sd_dailySales" & Date & ".xls", _
hasfieldnames:=True

End Function

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It was supposed to create a new file. Apparently, it is trying to put
the data in an existing file. Since that file "sd_dailySales4/8/05.xls.
Ah, ha... Have to reformat the date so it doesn't have "/" in it.

Try this:

filename:="c:\my documents\sd_dailySales_" & _
Format(Date,"YYYYMMDD") & ".xls", _

It should produce a file name like this:

sd_dailySales_20040408.xls

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlcoLIechKqOuFEgEQJaswCgpUOQ1gJ+DSVf0n3EPN6D+piJN3MAnA5E
PqdszuufORk9ZV+AePMPPCcq
=/pGX
-----END PGP SIGNATURE-----

 

[chad.wright]

it is only creating one excel file and then over writing the data
inside it.

 

[MGFoster]

it is only creating one excel file and then over writing the data
inside it.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you run the code once a day there will be a new file for each day
(Date() returns the current date). If you are writing the data more
than once a day then you need a time value in the file name:

filename:="c:\my documents\sd_dailySales_" & _
Format(Now(),"yyyymmdd_hhnnss") & ".xls", _

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlcsdYechKqOuFEgEQKLfACgvkmssZEr6Zm5BQs0fN+3icxKm4EAmwcG
CjxV7gTahjfWy+48i+qMHwmv
=QLmt
-----END PGP SIGNATURE-----

 

[chad.wright]

is there a way to just have one excel file no matter how many times a
day, week, year i run it?

 

[PC Datasheet]

Use the Kill statement to delete the previous Excel file and then run the
TransferSpreadsheet method and it will create a new Excel file with the same
name but contain your new data.
Kill pathname

 

[chad.wright]

I think that everyone is not understanding me clearly.

i have a form that at the end of shift each supervisor runs that
creates a excel spreadsheet and saves it to the server so our person
that handles all sales and checks to see if there are any errors or
sold to wrong states and so on uses. the way the form is set up is that
it has a button that is linked to vb code in the on click event. here
is the code i am currently using:

Function SD_exportToExcel()

DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tablename:="qrySD_Sales", _
filename:="C:\MY DOCUMENTS\SD_dailySales.xls", _
hasfieldnames:=True

End Function

here is the sql string of the query being used to get the data:

SELECT dbo_InventoryFairfield.CRCallDateTime,
dbo_InventoryFairfield.ArrivlDate,
dbo_InventoryFairfield.CRCallResultCode, dbo_InventoryFairfield.Office,
dbo_InventoryFairfield.FirstName, dbo_InventoryFairfield.MiddleInt,
dbo_InventoryFairfield.LastName, dbo_InventoryFairfield.SpouseName,
dbo_InventoryFairfield.Address, dbo_InventoryFairfield.City,
dbo_InventoryFairfield.State, dbo_InventoryFairfield.Zip,
dbo_InventoryFairfield.CRAreaCode, dbo_InventoryFairfield.CRExchange,
dbo_InventoryFairfield.CRNumber, dbo_InventoryFairfield.BusPhone,
dbo_InventoryFairfield.Rate, dbo_InventoryFairfield.VfrCamp,
dbo_InventoryFairfield.NoAdults, dbo_InventoryFairfield.NoKids,
dbo_InventoryFairfield.Comments1, dbo_InventoryFairfield.CcAmt1,
dbo_InventoryFairfield.CcAmt2, dbo_InventoryFairfield.CcAuthorize1,
dbo_InventoryFairfield.CcAuthorize2, dbo_InventoryFairfield.CrsID2,
dbo_InventoryFairfield.Email, dbo_InventoryFairfield.CRAgentID,
dbo_InventoryFairfield.VfrID
FROM dbo_InventoryFairfield
GROUP BY dbo_InventoryFairfield.CRCallDateTime,
dbo_InventoryFairfield.ArrivlDate,
dbo_InventoryFairfield.CRCallResultCode, dbo_InventoryFairfield.Office,
dbo_InventoryFairfield.FirstName, dbo_InventoryFairfield.MiddleInt,
dbo_InventoryFairfield.LastName, dbo_InventoryFairfield.SpouseName,
dbo_InventoryFairfield.Address, dbo_InventoryFairfield.City,
dbo_InventoryFairfield.State, dbo_InventoryFairfield.Zip,
dbo_InventoryFairfield.CRAreaCode, dbo_InventoryFairfield.CRExchange,
dbo_InventoryFairfield.CRNumber, dbo_InventoryFairfield.BusPhone,
dbo_InventoryFairfield.Rate, dbo_InventoryFairfield.VfrCamp,
dbo_InventoryFairfield.NoAdults, dbo_InventoryFairfield.NoKids,
dbo_InventoryFairfield.Comments1, dbo_InventoryFairfield.CcAmt1,
dbo_InventoryFairfield.CcAmt2, dbo_InventoryFairfield.CcAuthorize1,
dbo_InventoryFairfield.CcAuthorize2, dbo_InventoryFairfield.CrsID2,
dbo_InventoryFairfield.Email, dbo_InventoryFairfield.CRAgentID,
dbo_InventoryFairfield.VfrID
HAVING (((dbo_InventoryFairfield.CRCallDateTime)>[Start Date mm/dd/yy]
And (dbo_InventoryFairfield.CRCallDateTime)<[End Date mm/dd/yy]) AND
((dbo_InventoryFairfield.CRCallResultCode) Like "S*") AND
((dbo_InventoryFairfield.Office) Like "SD"));

i can't really have it delete the old spreadsheet i just need to be
able to add to the old spreadsheet. that way if we need to go back and
check a sale from a previous date we can just open the excel
spreadsheet and check it. this report could be run from one date to a
weeks worth at a time.

thanks

 

[Frederick Wilson]

I am sure this thought might have occurred already but why not give
access to the database to the person who is suppose to check the
informations.

Notwithstanding that,

The only way I see of doing this is opening the excel file and finding
the last row completed and start writing to the file from there.

I think that everyone is not understanding me clearly.

i have a form that at the end of shift each supervisor runs that
creates a excel spreadsheet and saves it to the server so our person
that handles all sales and checks to see if there are any errors or
sold to wrong states and so on uses. the way the form is set up is that
it has a button that is linked to vb code in the on click event. here
is the code i am currently using:

Function SD_exportToExcel()

DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tablename:="qrySD_Sales", _
filename:="C:\MY DOCUMENTS\SD_dailySales.xls", _
hasfieldnames:=True

End Function

here is the sql string of the query being used to get the data:

SELECT dbo_InventoryFairfield.CRCallDateTime,
dbo_InventoryFairfield.ArrivlDate,
dbo_InventoryFairfield.CRCallResultCode, dbo_InventoryFairfield.Office,
dbo_InventoryFairfield.FirstName, dbo_InventoryFairfield.MiddleInt,
dbo_InventoryFairfield.LastName, dbo_InventoryFairfield.SpouseName,
dbo_InventoryFairfield.Address, dbo_InventoryFairfield.City,
dbo_InventoryFairfield.State, dbo_InventoryFairfield.Zip,
dbo_InventoryFairfield.CRAreaCode, dbo_InventoryFairfield.CRExchange,
dbo_InventoryFairfield.CRNumber, dbo_InventoryFairfield.BusPhone,
dbo_InventoryFairfield.Rate, dbo_InventoryFairfield.VfrCamp,
dbo_InventoryFairfield.NoAdults, dbo_InventoryFairfield.NoKids,
dbo_InventoryFairfield.Comments1, dbo_InventoryFairfield.CcAmt1,
dbo_InventoryFairfield.CcAmt2, dbo_InventoryFairfield.CcAuthorize1,
dbo_InventoryFairfield.CcAuthorize2, dbo_InventoryFairfield.CrsID2,
dbo_InventoryFairfield.Email, dbo_InventoryFairfield.CRAgentID,
dbo_InventoryFairfield.VfrID
FROM dbo_InventoryFairfield
GROUP BY dbo_InventoryFairfield.CRCallDateTime,
dbo_InventoryFairfield.ArrivlDate,
dbo_InventoryFairfield.CRCallResultCode, dbo_InventoryFairfield.Office,
dbo_InventoryFairfield.FirstName, dbo_InventoryFairfield.MiddleInt,
dbo_InventoryFairfield.LastName, dbo_InventoryFairfield.SpouseName,
dbo_InventoryFairfield.Address, dbo_InventoryFairfield.City,
dbo_InventoryFairfield.State, dbo_InventoryFairfield.Zip,
dbo_InventoryFairfield.CRAreaCode, dbo_InventoryFairfield.CRExchange,
dbo_InventoryFairfield.CRNumber, dbo_InventoryFairfield.BusPhone,
dbo_InventoryFairfield.Rate, dbo_InventoryFairfield.VfrCamp,
dbo_InventoryFairfield.NoAdults, dbo_InventoryFairfield.NoKids,
dbo_InventoryFairfield.Comments1, dbo_InventoryFairfield.CcAmt1,
dbo_InventoryFairfield.CcAmt2, dbo_InventoryFairfield.CcAuthorize1,
dbo_InventoryFairfield.CcAuthorize2, dbo_InventoryFairfield.CrsID2,
dbo_InventoryFairfield.Email, dbo_InventoryFairfield.CRAgentID,
dbo_InventoryFairfield.VfrID
HAVING (((dbo_InventoryFairfield.CRCallDateTime)>[Start Date mm/dd/yy]
And (dbo_InventoryFairfield.CRCallDateTime)<[End Date mm/dd/yy]) AND
((dbo_InventoryFairfield.CRCallResultCode) Like "S*") AND
((dbo_InventoryFairfield.Office) Like "SD"));

i can't really have it delete the old spreadsheet i just need to be
able to add to the old spreadsheet. that way if we need to go back and
check a sale from a previous date we can just open the excel
spreadsheet and check it. this report could be run from one date to a
weeks worth at a time.

thanks

 

[Van T. Dinh]

I always wonder why we have to create duplicated and fragmented set of data
to be used in different applications.

The more efficient way is to develop the database / user interface so that
the person in the Sales office can perform the required function using the
database and give him/her access to the appropriate section(s) of the
database.

Technically, what you asked can be done by creating a Recordset based on the
Query, automating Excel, opening the existing spreadsheet, finding
appropriate place to paste and then use the CopyFromRecordset method to
paste the required rows from the Recordset.

--
HTH
Van T. Dinh
MVP (Access)

I think that everyone is not understanding me clearly.

i have a form that at the end of shift each supervisor runs that
creates a excel spreadsheet and saves it to the server so our person
that handles all sales and checks to see if there are any errors or
sold to wrong states and so on uses. the way the form is set up is that
it has a button that is linked to vb code in the on click event. here
is the code i am currently using:

Function SD_exportToExcel()

DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tablename:="qrySD_Sales", _
filename:="C:\MY DOCUMENTS\SD_dailySales.xls", _
hasfieldnames:=True

End Function

here is the sql string of the query being used to get the data:

SELECT dbo_InventoryFairfield.CRCallDateTime,
dbo_InventoryFairfield.ArrivlDate,
dbo_InventoryFairfield.CRCallResultCode, dbo_InventoryFairfield.Office,
dbo_InventoryFairfield.FirstName, dbo_InventoryFairfield.MiddleInt,
dbo_InventoryFairfield.LastName, dbo_InventoryFairfield.SpouseName,
dbo_InventoryFairfield.Address, dbo_InventoryFairfield.City,
dbo_InventoryFairfield.State, dbo_InventoryFairfield.Zip,
dbo_InventoryFairfield.CRAreaCode, dbo_InventoryFairfield.CRExchange,
dbo_InventoryFairfield.CRNumber, dbo_InventoryFairfield.BusPhone,
dbo_InventoryFairfield.Rate, dbo_InventoryFairfield.VfrCamp,
dbo_InventoryFairfield.NoAdults, dbo_InventoryFairfield.NoKids,
dbo_InventoryFairfield.Comments1, dbo_InventoryFairfield.CcAmt1,
dbo_InventoryFairfield.CcAmt2, dbo_InventoryFairfield.CcAuthorize1,
dbo_InventoryFairfield.CcAuthorize2, dbo_InventoryFairfield.CrsID2,
dbo_InventoryFairfield.Email, dbo_InventoryFairfield.CRAgentID,
dbo_InventoryFairfield.VfrID
FROM dbo_InventoryFairfield
GROUP BY dbo_InventoryFairfield.CRCallDateTime,
dbo_InventoryFairfield.ArrivlDate,
dbo_InventoryFairfield.CRCallResultCode, dbo_InventoryFairfield.Office,
dbo_InventoryFairfield.FirstName, dbo_InventoryFairfield.MiddleInt,
dbo_InventoryFairfield.LastName, dbo_InventoryFairfield.SpouseName,
dbo_InventoryFairfield.Address, dbo_InventoryFairfield.City,
dbo_InventoryFairfield.State, dbo_InventoryFairfield.Zip,
dbo_InventoryFairfield.CRAreaCode, dbo_InventoryFairfield.CRExchange,
dbo_InventoryFairfield.CRNumber, dbo_InventoryFairfield.BusPhone,
dbo_InventoryFairfield.Rate, dbo_InventoryFairfield.VfrCamp,
dbo_InventoryFairfield.NoAdults, dbo_InventoryFairfield.NoKids,
dbo_InventoryFairfield.Comments1, dbo_InventoryFairfield.CcAmt1,
dbo_InventoryFairfield.CcAmt2, dbo_InventoryFairfield.CcAuthorize1,
dbo_InventoryFairfield.CcAuthorize2, dbo_InventoryFairfield.CrsID2,
dbo_InventoryFairfield.Email, dbo_InventoryFairfield.CRAgentID,
dbo_InventoryFairfield.VfrID
HAVING (((dbo_InventoryFairfield.CRCallDateTime)>[Start Date mm/dd/yy]
And (dbo_InventoryFairfield.CRCallDateTime)<[End Date mm/dd/yy]) AND
((dbo_InventoryFairfield.CRCallResultCode) Like "S*") AND
((dbo_InventoryFairfield.Office) Like "SD"));

i can't really have it delete the old spreadsheet i just need to be
able to add to the old spreadsheet. that way if we need to go back and
check a sale from a previous date we can just open the excel
spreadsheet and check it. this report could be run from one date to a
weeks worth at a time.

thanks

 

[drmojo418]

the reason for creating a excel file is when we get a new import of
data the older data is taken out of the database (server) which in
return removes sales as well (don't ask i didn't create the database).

with the excel file we will always have the sales made no matter how
far back.

the problem with doing what you said is that i am not all that good
with vb that is why i am posting here. i got the orginal code from a
access book that i have.

 

[Van T. Dinh]

I think this is a slightly strange way of storing / archiving data. Access
is designed to store much more structured data than Excel.

You are limiting to 64K rows in Excel (97 to 2002, not sure about 2003)
while Access can accommodate many more Records than 64K.

Blame the original developer ...

 

[drmojo418]

i understand that excel is not a good source of data storage but i have
to use what i have to use. do you know of a book that would be good for
me to look at to figure out how to build the code to update the excel
file.

thanks
chad

 

[Van T. Dinh]

Probably none of the books will have exactly the code you want but virtually
all Access *programming* books (including some general books that touch the
programming side of Access) will cover automating Excel from Access.

Basically, the steps you require in VBA code are as follows:

* Create a Recordset of what the rows you need to transfer to the Excel
file.
* Automate Excel application and open the existing Excel file.
* Find the last non-empty row in the Excel spreadsheet (using the Excel's
End method)
* From the above, work out the top-left cell of the destination block in the
spreadsheet
* Check that the Excel spreadsheet still got enough empty rows for pasting
from Recordset..
* Use the Excel's CopyFromRecordset method to paste the data in the
Recordset starting from the above top-left cell of the destination block.
* Save the Excel spreadshhet and close the Access application object.
* Close the Recordset and destroy the objects (setting object variables to
nothing).

Most of the technical terms I mentioned above are in Access and Excel VBA
help and most of the relevant Help topics will have code examples. The
integration is yours, though.

 

[Ömer Ayzan]

Let me join the discussion.
Assuming that u once created the "SD_dailySales.xls" on that button click
use getobject to open that file
Private sub btn_click
dim strFile as string
dim rngCurRange as range
dim lngRowCnt as long
dim varData as variant

dim myXlWB as excel.workbook
strFile = "C:\MY DOCUMENTS\SD_dailySales.xls",
Set myXlWb = GetObject(strFile)
' Let us assume that the name of the sheet u have data is Sheet1
with myXlWb
.sheets("Sheet1").activate
set rngCurRange=range("A1").CurrentRegion
lngRowCnt=rngCurRange.Rows.Count ' This gives u the end of
data that is already in the sheet
' Now u can loop thru ur data which is obtained by ur qry.
' Lets say ur data in varData thru GetRows
end with
TransferToXL myXlWb,"Sheet1",varData,lngRowCnt+1
end sub

Private Sub TransferToXL(XlWb As excel.Workbook, ActSht As String, DataToTr
As Variant, StartRow as long)
Dim lngFldCnt As Long
Dim lngRowCnt As Long
Dim I As Long
Dim J As Long
Dim xlSht As excel.Worksheet

Set xlSht = XlWb.Sheets(ActSht)

lngFldCnt = UBound(DataToTr, 1)
lngRowCnt = UBound(DataToTr, 2)

For J = startRow To lngRowCnt+startRow
For I = 0 To lngFldCnt
xlSht.Cells(J + 2, I + 1) = DataToTr(I, J)
Next I
XlWb.Application.StatusBar = lngRowCnt + 1 & "/" & J + 1 & " records
so far"
Next J
XlWb.Application.StatusBar = False
End Sub

This code however slow does what u want. U can refine it. for ur own
requierments.

Ömer Ayzan

I always wonder why we have to create duplicated and fragmented set of data
to be used in different applications.

The more efficient way is to develop the database / user interface so that
the person in the Sales office can perform the required function using the
database and give him/her access to the appropriate section(s) of the
database.

Technically, what you asked can be done by creating a Recordset based on
the
Query, automating Excel, opening the existing spreadsheet, finding
appropriate place to paste and then use the CopyFromRecordset method to
paste the required rows from the Recordset.

--
HTH
Van T. Dinh
MVP (Access)

I think that everyone is not understanding me clearly.

i have a form that at the end of shift each supervisor runs that
creates a excel spreadsheet and saves it to the server so our person
that handles all sales and checks to see if there are any errors or
sold to wrong states and so on uses. the way the form is set up is that
it has a button that is linked to vb code in the on click event. here
is the code i am currently using:

Function SD_exportToExcel()

DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tablename:="qrySD_Sales", _
filename:="C:\MY DOCUMENTS\SD_dailySales.xls", _
hasfieldnames:=True

End Function

here is the sql string of the query being used to get the data:

SELECT dbo_InventoryFairfield.CRCallDateTime,
dbo_InventoryFairfield.ArrivlDate,
dbo_InventoryFairfield.CRCallResultCode, dbo_InventoryFairfield.Office,
dbo_InventoryFairfield.FirstName, dbo_InventoryFairfield.MiddleInt,
dbo_InventoryFairfield.LastName, dbo_InventoryFairfield.SpouseName,
dbo_InventoryFairfield.Address, dbo_InventoryFairfield.City,
dbo_InventoryFairfield.State, dbo_InventoryFairfield.Zip,
dbo_InventoryFairfield.CRAreaCode, dbo_InventoryFairfield.CRExchange,
dbo_InventoryFairfield.CRNumber, dbo_InventoryFairfield.BusPhone,
dbo_InventoryFairfield.Rate, dbo_InventoryFairfield.VfrCamp,
dbo_InventoryFairfield.NoAdults, dbo_InventoryFairfield.NoKids,
dbo_InventoryFairfield.Comments1, dbo_InventoryFairfield.CcAmt1,
dbo_InventoryFairfield.CcAmt2, dbo_InventoryFairfield.CcAuthorize1,
dbo_InventoryFairfield.CcAuthorize2, dbo_InventoryFairfield.CrsID2,
dbo_InventoryFairfield.Email, dbo_InventoryFairfield.CRAgentID,
dbo_InventoryFairfield.VfrID
FROM dbo_InventoryFairfield
GROUP BY dbo_InventoryFairfield.CRCallDateTime,
dbo_InventoryFairfield.ArrivlDate,
dbo_InventoryFairfield.CRCallResultCode, dbo_InventoryFairfield.Office,
dbo_InventoryFairfield.FirstName, dbo_InventoryFairfield.MiddleInt,
dbo_InventoryFairfield.LastName, dbo_InventoryFairfield.SpouseName,
dbo_InventoryFairfield.Address, dbo_InventoryFairfield.City,
dbo_InventoryFairfield.State, dbo_InventoryFairfield.Zip,
dbo_InventoryFairfield.CRAreaCode, dbo_InventoryFairfield.CRExchange,
dbo_InventoryFairfield.CRNumber, dbo_InventoryFairfield.BusPhone,
dbo_InventoryFairfield.Rate, dbo_InventoryFairfield.VfrCamp,
dbo_InventoryFairfield.NoAdults, dbo_InventoryFairfield.NoKids,
dbo_InventoryFairfield.Comments1, dbo_InventoryFairfield.CcAmt1,
dbo_InventoryFairfield.CcAmt2, dbo_InventoryFairfield.CcAuthorize1,
dbo_InventoryFairfield.CcAuthorize2, dbo_InventoryFairfield.CrsID2,
dbo_InventoryFairfield.Email, dbo_InventoryFairfield.CRAgentID,
dbo_InventoryFairfield.VfrID
HAVING (((dbo_InventoryFairfield.CRCallDateTime)>[Start Date mm/dd/yy]
And (dbo_InventoryFairfield.CRCallDateTime)<[End Date mm/dd/yy]) AND
((dbo_InventoryFairfield.CRCallResultCode) Like "S*") AND
((dbo_InventoryFairfield.Office) Like "SD"));

i can't really have it delete the old spreadsheet i just need to be
able to add to the old spreadsheet. that way if we need to go back and
check a sale from a previous date we can just open the excel
spreadsheet and check it. this report could be run from one date to a
weeks worth at a time.

thanks

 

[Van T. Dinh]

Check EXcel VBA on CopyFromRecordset Method. The code required will be much
shorter and more efficient.

 

[drmojo418]

now would all that code go before or after what i already have. or do i
make a new sub and put it where?

thanks