Export columns (table) from access to excel

[dacodac]

Hi everyone,


I got an issue, and I would like some help please. I will try to be clear.
I have my table from access (with 4 columns), and I would like to export these datas, but not like this (it would be much easier) into one excel file which collect the data.

I attached my files, and I filled the excel file with a manually example.example.zip

As you could see if you download it, my datas will depend on my first column (access; "categorie") to create a new column into the excel file, then it’s only to copy datas.
It checks if categorie already existed:
If no then add new column (excel) with the name and others datas (town, company, work) in row
If yes then add into the row the datas (Work have to match with Categorie)

I don’t know from where to start, I was thinking to put into an array my column Categorie (access), identify the different name and add column in my excel file.
Or I was testing with query tables and then create the column into my excel file. But with this one nothing is really automatic.

Many thanks for your help!
Regards

[Stingaway]

Why not create a query in the database that houses all the data you want in the format you wish to see it, then export the query to the excel spreadsheet? You can add your columns through using IIF statements in Access on the fly as the query is run, that way your format will always be as expected once exported to Excel.

[dacodac]

Hi thanks for this advice!
I will look at it tomorrow morning!
First thing I'm thinking that I will have some column (5-6) in my excel workbook and between my data from access. Is that a problem?
Do you know where I could find an example?

Thank you!

[dacodac]

Hey,

I have used another query, now i can separate my categorie with this code :
Sub WorkC()

Dim Rs As ADODB.Recordset
Dim sSQL As String
Dim VCategorie As String
sSQL = "SELECT DISTINCT Categorie FROM Table1"
Set Rs = New ADODB.Recordset
Rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Rs.MoveFirst
Do Until Rs.EOF
VCategorie = Rs("Categorie")
Debug.Print VCategorie
Rs.MoveNext
Loop
Rs.Close
End Sub


Now I would like to add new column into a workbook values I got from my variable VCategorie.

Is someone could help me please?

[dacodac]

And I forgot to add that I tried with your advice:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "VCategorie", "C:\Path to my excel workbook", , "Sheet1"

But still doesn't work ...

On progress!