How to Maintain Row Order when Exporting 1

[slowmike]

I want to export a table from Access to Excel without changing the order of the rows in the table. However, when I export it (either manually or using OutputTo) the order of the rows is different in the spreadsheet than it is in the table, even though I have the table indexed. Is there a way to ensure that the row order in the table is maintained in the spreadsheet? Thanks.

 

[vbajock]

You might want to try putting your table in a query, using the query grid to set the sort column to ascending/descending, and then export the query instead of the table.

 

[slowmike]

Thanks. I have a code set up that runs through the 100 or so tables in the database and exports them to Excel. Do I have to create a query for every table I have in the database? Is there a way to automate this in a module or macro or do I have to do it manually? Thanks again for your response.

 

[vbajock]

You can use a macro. First, create all the queries. Next, open the macro window. In the first line put Setwarnings, set to NO, this will turn off warning messages that appear on the screen that require you to click and OK or Cancel.

Next line put Hourglass YES.
On each of the next lines put TransferSpreadsheet and fill out the dialog box with the correct settings for your export on each line.

On the last two lines, Setwarnings, set to YES to turn them back on, Hourglass, set to NO to turn hourglass off when macro finishes.

If you want to debug your macro, turn Setwarnings in the first line ON so you can review any messages. There is a catch in all this - if the Excel file you are exporting already exists, Access will append the new export to a new worksheet in the file.

If you want to automate it in code, look at the TransferSpreadsheet method in the help file. The example in the help file shows how to do this. Using code, you can solve the problem of the existing file by using a KILL [filename] command to kill the old stuff before you create the new.

 

[slowmike]

Thanks, that helps. If only there was a way to automate the query creation. Anyway, thanks a lot for your help.

 

[vbajock]

If all the queries are the same, but use different variables, you can automate it by writing the query to a string and processing the string through ADO (or DAO if your old-fashioned). Are you familiar with either of these?

 

[slowmike]

I am not. I know how to used the docmd.runsql command to run update and select statements in VBA, but I don't know how to get it to export a query that I haven't created and named manually. Ideally, I would like to run a line that looked like this:

 DoCmd.OutputTo  "select * from " & ret1 & " order by f1 asc", "MicrosoftExcel(*.xls)", filepath

where ret1 is a variable that is populated from a cursor that runs through a table of the names of all the tables in the database. I just don't know how to get the code to recognize the select statement as a table.

 

[vbajock]

This is pretty general and will probably require some rework:

Set a reference to ADOX.
Plug this in and pass it the required parameters

Sub CreateQuery(strDBPath As String, _
strSQL As String, _
strQryName As String)

Dim catDB As ADOX.Catalog
Dim cmd As ADODB.Command

Set catDB = New ADOX.Catalog
' Open the catalog.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDBPath


Set cmd = New ADODB.Command
' Define a Command object to contain the query's SQL, and then
' save it to the database's Views collection. StrSQL must
' contain only a SELECT statement.


cmd.CommandText = strSQL

catDB.Views.Append strQryName, cmd

Set catDB = Nothing

End Sub

your Calling code would look something like:

Dim sqlStr as string
dim i as integer
dim strpath as string
dim strQryName as string
dim ret1 as string


strpath="C:\MYDB\MYDB.MDB"

i=0

Do
i=i+1

strQryName="QueryNo"+Trim$(Str$(i))

'some code to fill your sql string variables

ret1=

sqlStr= "select * from " & ret1 & " order by f1 asc"

CreateQuery(strDBPath, _
strSQL, _
strQryName)

docmd.transferspreadsheet blaHBLAH strQryName

Loop until WHATEVER

 

[slowmike]

Shweet. Won't I be cool if I get this to work. I'll let you know if I run in to any problems. Thanks a lot for your help.

 

[slowmike]

Me again. I think I have everything set up correctly, but when it tries to establish the connection to open the catalog (catDB.ActiveConnection = ...), it cannot because the database is already open. Is there an easy way around this?

 

[MinusM]

You can use querydefs as a simpler solution
Create a temporary query tmpQ in the database if you can reuse that name and set sql each time you export

currentdb.querydefs(tmpq).sql = "select * from " & ret1 & " order by f1 asc"

To create query programmatically

Dim qdfNew As QueryDef
' Create permanent QueryDef.
Set qdfNew = currentdb.CreateQueryDef("NewQueryDef","select * from " & ret1 & " order by f1 asc&quot

Set qdfNew = nothing


Best of luck

 

[p27br]

change catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDBPath

to
catDB.ActiveConnection = currentproject.connection