Export to Excel moves columns left when a column is NULL

  • Thread starter Thread starter Sverre
  • Start date Start date
S

Sverre

We are exporting query results by highlighting the entire
query result in Access and pasting it into Excel.
Sometimes for some records, when a column's value is NULL,
the value in the next column is inserted into the column
whose value is NULL in Access. This can occur for 2 - 25
records, then it is aligned correctly whether there are
NULLs in that column or not. Then the column with NULL
values in the Access query will have values from the next
column again.
Does anybody have thoughts on causes and fixes?
Thanks.
 
Hi there!

I also have some problems with moving data from form to
exceldocument. I have the following code, but I get the
errormessage "To few parameters, expected 2." Does anyone
of you know what's wrong? Please help me if you can!!!

Private Sub ExcelKnp_Click()
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Application.Visible = True
Set ExcelBook = ExcelApp.Workbooks.Add

ExcelBook.Worksheets(1).Cells(1, 1).Font.Size = 10
ExcelBook.Worksheets(1).Cells(1, 1).Font.Bold = True
ExcelBook.Worksheets(1).Cells(1, 1).Value = "e2upnr"

ExcelBook.Worksheets(1).Cells(1, 2).Font.Size = 10
ExcelBook.Worksheets(1).Cells(1, 2).Font.Bold = True
ExcelBook.Worksheets(1).Cells(1, 2).Value = "e3vgnr"

Set oRS = CreateObject("ADODB.RecordSet")
Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = "DRIVER={Microsoft Access
Driver (*.mdb)}; DBQ=C:\M\MM.mdb"
bSucess = oConn.Open

Dim q
q = " SELECT Forms!AktUpn!e2upnr, Forms!AktUpn!
AktUpnUnd!e3vgnr"

Dim i
i = 2

Set oRS = oConn.Execute(q)
Do While Not oRS.EOF

ExcelBook.Worksheets(1).Cells(i, 1).Value = oRS
("e2upnr")
ExcelBook.Worksheets(1).Cells(i, 2).Value = oRS
("e3vgnr")

oRS.MoveNext
i = i + 1
Loop

Set oRS = Nothing
bSucsess = oConn.Close
Set oConn = Nothing

ExcelBook.SaveAs "C:/Majsan.xls"
ExcelApp.Application.Quit
Set ExcelApp = Nothing
End Sub

Cheers!
 
Back
Top