export union query to excel, delete the first row

[benjamin.grimm]

hello

i export a union query to excel by the following code:

Private Sub Befehl0_Click()
' Übersicht aufrufen
 
Dim xlApp As Object ' Excel.Application
Dim xlBook As Object ' Excel.Workbook
Dim xlSheet As Object ' Excel.Worksheet
Dim rst As DAO.Recordset
'Excel-Objekt öbffnen und Workbook hinzufügen
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open("E:\Michi\Export2.xlsx")
'In Tabellenblatt Gesamt schreiben
Set xlSheet = xlBook.Worksheets(1)
Set rst = CurrentDb.OpenRecordset("Vorratseinplannung_Setra_Gesamt")
xlSheet.Cells(7, 13 + Month(Date)).CopyFromRecordset rst
rst.Close
 
'Objekte schließen
Set rst = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

How can i delte the first row of the querry? without doing a new querry?

greetz benjamin

 

[Brianwarnock]

I don't have ACCESS but in Excel you would simply say
sheetref.Rows(1).Delete
therefore does

xlSheet.Rows(1).Delete

work?

Brian

 

[benjamin.grimm]

hmm,

i dont want to delete a row in excel.

I want to delete one column in the querry.

Do you know what i mean?

 

[Frothingslosh]

The thing is, by their nature, queries return ALL data matching the criteria in the query. If the first row has data you want excluded, then you need to add criteria that specifically excludes that data.

If, on the other hand, you want to remove the first line no matter what is in it, Brian gave you what is probably the easiest and fastest approach. The only other method that comes to mind (that doesn't involve a pretty obnoxious amount of coding and query manipulation via TOP) is using the union query to append the records to a table, delete the first record via code, and then transferring the data to the spreadsheet...which is just a really long way of doing what Brian already showed you.

 

[mh123]

hm you initially said you wanted to delete a row and now you want to delete a column? You could specify which columns from the query you put into the excel sheet I believe if that's what you're after?

 

[benjamin.grimm]

Sorrry i meant column.

The first column in the union querry.

I have those 2 querry:

SQL 1: 2_geplannt_CC_400

TRANSFORM Count(G.Verkaufsorg) AS AnzahlvonVerkaufsorg 
SELECT    T.Feld1 AS Land, A.[AnzahlvonBustyp (Vertrieb)] AS Istbestand 
FROM      Abfrage_Zählen_Comfort_Class_400 AS A 
          INNER JOIN (Tabelle_Länder_DE AS T 
                      LEFT JOIN 2_geplannt_CC_400 AS G 
                      ON T.Feld1 = G.[Land Regulierer]) 
          ON A.Land = T.Feld1 
GROUP BY  T.Feld1, A.[AnzahlvonBustyp (Vertrieb)] 
PIVOT     Format([Bauplatztermin],"mmm") In ("Apr","Mai","Jun","Jul","Aug","Sep","Okt","Nov","Dez","Jan","Feb","Mrz");

SQL2: 2_geplannt_CC_400_TG

TRANSFORM Count(G.Verkaufsorg) AS AnzahlvonVerkaufsorg 
SELECT    T.Feld1 AS Land, A.[AnzahlvonBustyp (Vertrieb)] AS Istbestand 
FROM      Abfrage_Zählen_Comfort_Class_400 AS A 
          INNER JOIN (Tabelle_Länder_ohne_DE AS T 
                      LEFT JOIN 2_geplannt_CC_400_TG AS G 
                      ON T.Feld1 = G.[Land Regulierer]) 
          ON A.Land = T.Feld1 
GROUP BY  T.Feld1, A.[AnzahlvonBustyp (Vertrieb)] 
PIVOT     Format([Bauplatztermin],"mmm") In ("Apr","Mai","Jun","Jul","Aug","Sep","Okt","Nov","Dez","Jan","Feb","Mrz");

So with the Union Querry i conect those 2 querries.

The i have the following columns

country, istbestand, apr, mai, jun, jul, aug, sep, okt, nov, dez, jan, feb, mrz

But i dont have to in my excel file the column country.

Do you know what i mean?

 

[mh123]

Ok, then I would suggest doing as Brian suggested earlier but just deleting column1 instead of row1 before the excel file is saved?
xlSheet.Column(1).Delete

HTH!

 

[Brianwarnock]

That should be columns with an s.

Brian