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]

For some reason Excel thinks those records have been populated with data... They are in the used range. The interoperation between Access and Excel is just poor in my opinion so this does not surprise me.

You could work around by selecting the row just past the end of the data. Next hit ctrl + shift + down arrow. Now with all the cells selected, delete the rows (I use alt+e then d). Save the sheet. Now it should behave as you expect.

 

[donbolinger]

That didn't do the trick. Thanks for your input, though.

 

[lameid]

I could not replicate your problem using Access 2003. Unless you mean that it goes past the last record of the data exported including those that are blank. In that case it is what I would expect.

 

[donbolinger]

Let's say you have a column with 100 rows of data and there are numerous blank cells within the column. When you press End then Down (or Ctrl+Down) in Excel, normally the cursor moves to the next blank cell in the column. In this case, it moves the cursor to the last cell of data in that column, which it should not do.

 

[lameid]

So if you have a 100 rows of data, are you saying it moves to row 101 (102 if there is a header) or to the last row (65,536 <-- the upper limit in XL 2003)?

 

[donbolinger]

Correct, it drops down to row 100.

 

[lameid]

Clearly it inserts the entire array of values. That makes sense to me.

 

[donbolinger]

Anyone know a solution to this? Is there any way to turn these "blank" cells back into truly blank cells?

 

[lameid]

At this point I'd recommend posting in the Office forum as this is more of an Excel issue than an Access issue.

The issue seems to be that when you copy, you end up with an even rectangular array of values when you paste into Excel.

Alternatively you could Automate Excel via access and write out the cells individually programmatically. This would be very slow. My expectation is that there is an easier way to fix this in the Excel environment than automating Excel via Access. I also expect that you could run into this aquiring data from multiple sources. If code is necessary a generic Excel VBA procedure would be better than forcing it from Access so that you can use it in any situation that crops up.

 

[Swi]

Trying something like this:

Private Sub Command1_Click()
Dim conn As ADODB.Connection
Dim numofrecs As Long
Set conn = New ADODB.Connection
With conn
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.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

Swi

 

[JerryKlmns]

Don

I 've seen this behevior when I export to an already existing excel using an existing worksheet. Old records reaching row 1234 and new ones happen to be only 556. The rest of the range (1234-556) is blank inteed but still seems to be a used range. Deleting rows or redefining the range never seemed to solve the problem. The cure is to delete the file and run the export.

This also takes extra time to complete the export.

 

[lameid]

JerryKlmns,

The issue is that when you export the 556 records to a new file that say only the first 540 are populated with data in one of the columns. He wants to be able to end + down arrow and end up in row 541 for the column instead of row 557.

I am guessing SWI's code would have the same problem as would any method exporting to Excel.

Don,
It's a long shot but I wonder what would happen if you exported to a comma separated value file (.csv) in Access and opened that in Excel. If you took my advice about the office forum, please link the thread here (copy and paste the word thread and number towards the top of the thread).

 

[SkipVought]

I can QUERY from Excel and the results do not exhibit this characteristic.

I can navigate the resultset, using [END] [DOWN ARROW] and find the next empty cell within the resultset in that column.

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

 

[lameid]

Skip,

I'm having trouble following your post.... Are you saying SWI's code does solve the problem?

 

[SkipVought]

The only change I might suggest to swi's code...

    .Execute "SELECT * INTO [Excel 8.0;" & "Database=C:\TEST.XLS].[Sheet1[b][red]$[/red][/b]] FROM [TEST]", numofrecs

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

 

[JerryKlmns]

lameid

It does it for rows and columns if the previos resultset had more fields & records.

 

[Swi]

The dollar sign should be omitted.

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

Swi

 

[donbolinger]

Swi,

I tried your code but I get this error message:

Run-time error '-2147467259 (80004005)':

The database has been placed in a state by user 'Admin' on machine '[computer name]' that prevents it from being opened or locked.

When I click Debug, it goes to the Open statement of the code.

Any ideas?

Thanks,
Don

 

[Swi]

It sounds like your database is open for exclusive access as that is the only way I can reproduce your error. If you have Access open close it and open it in something other than exclusive mode.

Swi