Exporting to Excel for large fields 1

[TheFitz]

Hi All,

I'm struggling with this one. I've tried to Export a table with 20ish fields to Excel some fields with 4000 characters in. I have tried 3 different ways:

a) Select the table and select Export - This Exports the data correctly, however, it puts a ' before all the text fields. ie instead of saying:

Joe Bloggs

in the field it states:

'Joe Bloggs

b) Open the table and copy the data by selecting all and Ctrl-C etc, and then pasting to Excel. This does not include the ' however, it only pulls a max of 255 characters!!

c) I tried TransferSpreadsheet and this worked exactly as a) did (As I expected it would).

I realise that Excel (.xls) format is not the best for this, however, the requirement is for this format. I also would prefer to not have to open Excel and run code from there.

Anyone got any ideas on this one, as I'm stumped.

Thanks for anytime you put into this query,

Regards,


Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[2Grandpa]

Use TransferSpreadsheet macro action, or VBA code's
DoCmd.TransferSpreadsheet method.

 

[TheFitz]

Hi Grandpa2,

As stated, I've tried both of these method's and they don't want to work correctly.

Thanks for your suggestion anyway,

Regards,

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[SeeThru]

How about export to CSV (or Tab separated), then open/convert to Excel?

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[TheFitz]

That's a possibility, but the user would prefer a direct Export to Excel, not CSV.

Thanks anyway,

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[henniec]

Good day,

I use a different method to export data from Access to Excel. I copy the whole table/query to the sheet with the steps indicated below. It is an extract of the method I use for all data exported to Excel and do most of the formatting from within Access.

If need you could trasfer the data to a different work sheet and then from within Excel append it to the table in Excel or do a row count via Access in Excel and use that number to append it from there.

Remember to synchronize your query with the columns in Excel if you copy directly into and existing table.

Hope this helps.

Hennie

Set objExcel = CreateObject("Excel.Application")
'Run qryMisaRSP and transfer data to sheet RSP for annual statistics.

Set rs1 = db.OpenRecordset("tblMisaRSP", dbOpenSnapshot)

'Set the object variable to reference the file you want to see.

With objXL
.Visible = True

Set objWkb = .Workbooks.Open(conWKB_NAME)

On Error Resume Next

Set objSht = objWkb.Worksheets(conSHT_NAME1) 'RSP
objWkb.Worksheets("RSP").Activate

objWkb.Windows("RSP").Visible = True

With objSht

'Copy data from the two record sets
.Range("A2").CopyFromRecordset rs1

 

[TheFitz]

That's great, but for some reason, it can't handle large fields of over 1,000 characters. Still trying to work out whether it's a transfer limitation or if there is another parameter that will help this work.

Thanks for the info hennie

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[TheFitz]

Just to take this further, there is a limit of 911 on cell characters to transfer using TransferRecordset. However, thanks must go to hennie as I was pointed in the right direction by this one. After googling the issue, I came across the following code:

Dim oQueryName As String 'this is a unique query identifier 
Dim oSQL As String 'this is the query statement 


' I left in the original CopyFromRecordset statement 
'Cells(1, 1).CopyFromRecordset oRecordset 


With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=MyODBC", _ 
Destination:=Range("A1")) 
   .CommandText = oSQL 
   .Name = oQueryName 
   .FieldNames = False 
   .RowNumbers = False 
   .FillAdjacentFormulas = False 
   .PreserveFormatting = True 
   .RefreshOnFileOpen = False 
   .BackgroundQuery = True 
   .RefreshStyle = xlInsertDeleteCells 
   .SavePassword = False 
   .SaveData = True 
   .AdjustColumnWidth = True 
   .RefreshPeriod = 0 
   .PreserveColumnInfo = True 
   .Refresh BackgroundQuery:=False 
   .EnableRefresh = False 'this is NOT equiv to unchecking "Save query 
definition." 
End With 


'delete the query table. this is equivalent to unchecking "Save query 
definition." 
ActiveSheet.QueryTables.Item(oQueryName).Delete

With a bit of manipulation I got it working in Access. Tests proved one cell contained 4416 charates, exactly as required.

Thanks hennie, I couldn't have done it without you.

Fitz

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[henniec]

Hi Fritz,

Glad it helped as that is the only way I do it. Did not know if there was any characther limit, but you solved the problem now.

If you need to know more about formatting from within Access I will be back at work tomorrow and could help you out there.

Let me know what you need and I may lead you in some way to do the formattting. I do a limitted formatting with graphs to.

Hennie

 

[TheFitz]

Hi Hennie,

That's great, I just needed the data in Xl. Thanks for you assistance.

Fitz

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!