Copy from Access to Excel - blank cells do not behave as blanks.

[donbolinger]

When I use the following Macro command:

RunCommand / OutputToExcel

It copies the data to Excel, and any blank cells appear to be blank however, when you use the End key plus the down arrow key to move to the next blank cell, it brings you to the bottom of the column. This leads me to believe there is something hidden in those blank cells but I can't figure out how to remove whatever might be in there.

This also happens when you just copy data from a table or query in Access and paste it into Excel.

Any ideas?
Thanks,
Don

 

[lameid]

I got the desired result using SWI's code although I modified it to get the current connection rather than open a new one. I had a locking issue as written.

Private Sub tester()
    Dim conn As ADODB.Connection
    Dim numofrecs As Long
    Set conn = CurrentProject.Connection 'Changed
    With conn
        '.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SEC\T\db.mdb;Persist Security Info=False"
        '.Open
        .Execute "SELECT * INTO [Excel 8.0;" & "Database=C:\TEST.XLS].[Sheet1] FROM [TEST]", numofrecs
    conn.Close
    End With
    Set conn = Nothing
    MsgBox numofrecs & " exported to Excel!", vbInformation
End Sub

For grins I also tested transferspreadsheet and it worked too...

docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"Test","c:\Test2.xls", Yes

Now it enven works with the OP method...

I outputed 2 columns with the second (right most) column having an empty cell. I wonder if column order is the underlying issue.

 

[SkipVought]

http://support.microsoft.com/kb/295646

How to Copy
Do not use the dollar sign syntax, for example [Sheet1$], when you refer to a sheet name as your destination. The destination workbook can exist or not exist; however, the destination sheet must not yet exist.

....

How to Append
Now that you are referring to an existing worksheet, you must use the standard dollar sign syntax, for example, [Sheet1$]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Swi]

Yes, Skip thank you for pointing that out. I should have highlighted that distinction.

Swi