Order of Data export to Excel

[Imran Ghani]

Hi! Its nice to be among the company of so many programming genius people. I
am exporting my query results from my Access form as :
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryinvrgstr",
"d:\invoice register.xls", True
I am getting all the data in the excel file, but the order of the data is
not being maintained. Kindly do guide me about how can the order of data can
also be maintained in the resulting excel sheet, as in the Access Report .
Thanks in advance.

 

[Stefan Hoffmann]

hi Imran or Ghani,

what is your first name?

Hi! Its nice to be among the company of so many programming genius people. I
am exporting my query results from my Access form as :
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryinvrgstr",
"d:\invoice register.xls", True
I am getting all the data in the excel file, but the order of the data is
not being maintained. Kindly do guide me about how can the order of data can
also be maintained in the resulting excel sheet, as in the Access Report .

The order is taken normally from your query named "qryinvrgstr". So to
ensure the correct order, open the query in design view and set the
appropriate order of the fields. Save it and try an reexport to Excel.
Now the data should have the same order.


mfG
--> stefan <--

 

[Imran Ghani]

Thanks Stefan, that worked fine and data is now arranged according to the
criteria of the query. Kindly, can you also guide about how to export the
aggregate fields at the end the access report to our excel worksheet.
Regards,
Imran.

 

[Stefan Hoffmann]

hi Imran,

Thanks Stefan, that worked fine and data is now arranged according to the
criteria of the query. Kindly, can you also guide about how to export the
aggregate fields at the end the access report to our excel worksheet.

You need to evaluate the number of exported records first, e.g. using
DCount(). Then you can use TransferSpreadsheet() again on the same file
giving it a destination range for your data,

Sub TransferSpreadsheet([TransferType As AcDataTransferType = acImport],
[SpreadsheetType As AcSpreadSheetType = acSpreadsheetTypeExcel97],
[TableName], [FileName], [HasFieldNames], [Range], [UseOA])
Member of Access.DoCmd

e.g. you need an extra query for the aggregation:

Dim Count As Long
Dim Range As String

Count = DCount("*", "qryInvrgstr") + 1
Range = "A" + CStr(Count)

DoCmd.TransferSpreadsheet _
acExport, acSpreadsheetTypeExcel97, _
"qryInvrgstrAggregationValues", "d:\invoice register.xls", _
, Range

Maybe you need to give it an explicit range end, e.g.

Range = "A" + CStr(Count) + ":H" + CStr(Count)


mfG
--> stefan <--

 

[Imran Ghani]

Hi Stefan,
Thanks for your guidance. I am getting the number of rows returned by the
query in excel sheet correctly. I have made a new query and got my aggregate
values from it at the end of the excel sheet according to your guidance but
unfortunately ,it's giving an error message
run-time error '3010':
table 'A244:H244' already exists. I'd appreciate your help to sort the error.
Regards,
Imran.

hi Imran,

Thanks Stefan, that worked fine and data is now arranged according to the
criteria of the query. Kindly, can you also guide about how to export the
aggregate fields at the end the access report to our excel worksheet.

You need to evaluate the number of exported records first, e.g. using
DCount(). Then you can use TransferSpreadsheet() again on the same file
giving it a destination range for your data,

Sub TransferSpreadsheet([TransferType As AcDataTransferType = acImport],
[SpreadsheetType As AcSpreadSheetType = acSpreadsheetTypeExcel97],
[TableName], [FileName], [HasFieldNames], [Range], [UseOA])
Member of Access.DoCmd

e.g. you need an extra query for the aggregation:

Dim Count As Long
Dim Range As String

Count = DCount("*", "qryInvrgstr") + 1
Range = "A" + CStr(Count)

DoCmd.TransferSpreadsheet _
acExport, acSpreadsheetTypeExcel97, _
"qryInvrgstrAggregationValues", "d:\invoice register.xls", _
, Range

Maybe you need to give it an explicit range end, e.g.

Range = "A" + CStr(Count) + ":H" + CStr(Count)


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Imran,

I have made a new query and got my aggregate
values from it at the end of the excel sheet according to your guidance but
unfortunately ,it's giving an error message
run-time error '3010':
table 'A244:H244' already exists. I'd appreciate your help to sort the error.

hmm, I can only guess without seeing your actual code. Better you post it.


mfG
--> stefan <--

 

[Imran Ghani]

Hi Stefan!
I have made another aggregate query and attached it with the first file.
Please find the code written as guided by you:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryinvrgstr",
"d:\invoice register.xls", True

Dim rep As String
Dim count As Long
Dim range As String
count = DCount("*", "qryinvrgstr") + 1
range = "A" + CStr(count) + ":F" + CStr(count)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryinvrgstraggregate", "d:\invoice register.xls", , range

Regards,
Imran.

 

[Imran Ghani]

Hi Stefan!

Thanks for your reply.

I have made another aggregate query and attached it with the first file.
Please find the code written as guided by you:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryinvrgstr",
"d:\invoice register.xls", True

Dim rep As String
Dim count As Long
Dim range As String
count = DCount("*", "qryinvrgstr") + 1
range = "A" + CStr(count) + ":H" + CStr(count)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryinvrgstraggregate", "d:\invoice register.xls", , range

Regards,
Imran.

 

[Imran Ghani]

Hi Stefan
Is it right that we cannot mention range while exporting our data to ms
excel, otherwise, it just gives an error and export process is not completed,
if its otherwise, then kindly guide me about how to export my aggregate
columns at the end of the excel report, correctly.
Regards,
Imran.

 

[Stefan Hoffmann]

hi Imran,

Is it right that we cannot mention range while exporting our data to ms
excel,

Correct. This was my fault.

You can use Excel automation:


Dim ea As Object 'Excel.Application
Dim rs As DAO.Recordset

Dim SQL As String

Set ea = CreateObject("Excel.Application")

ea.Visible = True
ea.Workbooks.Add

Set rs = CurrentDb.OpenRecordset("yourQuery", dbOpenSnapshot)
ea.Range("A1").CopyFromRecordset rs
rs.Close

Set rs = CurrentDb.OpenRecordset("yourOtherQuery", dbOpenSnapshot)
ea.Range("A" + CStr(NumOfRecords)).CopyFromRecordset rs
rs.Close

Set rs = Nothing
Set ea = Nothing


mfG
--> stefan <--

 

[Imran Ghani]

Hi Stefan,
Thanks for your kind guidance. Kindly also mention, where to put my code, in
excel, as I am a novice in it.
Regards,
Imran.

 

[Imran Ghani]

Hi Stefan,
I have made a macro in excel, and trying to run it in the excel sheet, but
with no success, so far. Kindly guide me about where to put the code in the
excel sheet, so that it can work properly.
Regards,
Imran.

 

[Imran Ghani]

Hi Stefan,
Thanks for your help. I'd applied the procedure but it's giving an exception
at
Run time error 3061
Set rs = CurrentDb.OpenRecordset("qryinvrgstr", dbOpenSnapshot)
of too few parameters, expected 3
I'd much appreciate your kind help.
Regards,
Imran.

 

[Stefan Hoffmann]

hi imran,

Thanks for your kind guidance. Kindly also mention, where to put my code, in
excel, as I am a novice in it.

You use this code in Access. Create a new module in the database window,
insert a new method:

Option Compare Database
Option Explicit

Public Sub ExportToExecl()

'place the code here.

End Sub

You can execute the code by pressing F5 while the cursor is located
between the Public Sub and End Sub lines.

You need to declare a variable NumOfRecords and fill it with the correct
number of records.


mfG
--> stefan <--

 

[Imran Ghani]

Hi Stefan,

Thanks for your helpful reply, but unfortunately I am getting the following
type of error:

Run time error 3061
too few parameters, expected 3

and the code highlighted is indicated below:
Set rs = CurrentDb.OpenRecordset("qryinvrgstr", dbOpenSnapshot)

I'd much appreciate your help in this regards,

Best Regards,
Imran.

 

[Stefan Hoffmann]

hi Imran,

Run time error 3061
too few parameters, expected 3

and the code highlighted is indicated below:
Set rs = CurrentDb.OpenRecordset("qryinvrgstr", dbOpenSnapshot)

This means, that your query qryinvrgstr contains parameters or typos. In
the case o parameters you need to fill them per code:

Dim qd As DAO.QueryDef

Set qd = CurrentDb.QueryDefs.Item("qryinvrgstr")

qd.Parameters.Item(0).Value = yourValuesHere
qd.Parameters.Item(1).Value = yourValuesHere
qd.Parameters.Item(2).Value = yourValuesHere
Set rs = qd.OpenRecordset(dbOpenSnapshot)

mfG
--> stefan <--

 

[Imran Ghani]

Hi Stefan,
Thanks for all your time and help, as I being a novice have to ask for even
minor things, not being able to correctly mention the parameter name in the
module code, so its giving the error message of "variable not defined".
My parameter value in the query is given as follows:
WHERE (((invrgstr.solnam)=forms!frmsolinvrgstr!cbosolnam) And
((invrgstr.invstatus)="full o/s"))
It would be much kind of you, if you'd guide me about specifying the value
of parameter correctly for the module. Thanks again.
Regards,
Imran.

 

[Stefan Hoffmann]

hi Imran,

My parameter value in the query is given as follows:
WHERE (((invrgstr.solnam)=forms!frmsolinvrgstr!cbosolnam) And
((invrgstr.invstatus)="full o/s"))
It would be much kind of you, if you'd guide me about specifying the value
of parameter correctly for the module. Thanks again.

These two conditions should not raise the error message you're getting.
The first one references a form and is automatically resolved if the
form is open when you execute the query, respectively the code. The
second one is using a literal.
So your error message seems originating from some typos.


mfG
--> stefan <--

 

[Imran Ghani]

Hi Stefan,

Thanks for your guidance. Can you please also guide me about how to refer to
the value of the parameter in the module

Dim qd As DAO.QueryDef
Set qd = CurrentDb.QueryDefs.Item("qryinvrgstr")
qd.Parameters.Item(0).Value = yourValuesHere 'over here ?

I am not able to successfully specify the correct value for the parameter in
the module and the same type of error message is generated.

Regards,
Imran.

 

[Imran Ghani]

Hi Stefan,
Thanks for your reply. There are no typos used in my query, as its just
using one parameter value that I'v mentioned in the previous post. Previously
I's using a query which'd three parameters, so the error message was
specifying about 3 missing values, now its mentioning about just one missing
value, in this query. I'd much appreciate your valuable guidance about how to
mention the value of the parameter in the module, posted by you.
Regards,
Imran.