How do I switch this from DAO to ADO

[QuietRiot]

basically i'm having an issue where I can only get 256 characters to export into excel with the code below. I'm under the impression that ADO will fix this. How can I switch it?

  Dim dbs As DAO.Database
    Dim reportarray As Variant
    Dim rstGetExportData As Recordset
    Dim rs As DAO.Recordset
    Dim objXL As Object
    Dim objCreateWkb As Object
    Dim objActiveWkb As Object
    Dim Qyear As String
    Dim Q As Long, Lastrow As Long, StartRow As Long, x As Long
    Dim strSQL As String
    Dim Yearr As String
    Set dbs = CurrentDb
    Set objXL = CreateObject("Excel.Application")
    'edit this if template is moved.
    Set objCreateWkb = objXL.Workbooks.Open("L:\production support Template.xlt") 'objXL.Workbooks.Add
    Set objActiveWkb = objXL.Application.ActiveWorkBook
    objXL.Visible = False
            
    strSQL = "SELECT DateReported, Issue, PersonReported, PteamMember,Cause, TimeInHrs, WorkType, Category," & _
    " NewRecur,Resolution,Tasks, ICC FROM ProductionSupport WHERE WorkType <> 'Regular' AND Month(DateReported) =" & Qyear & " AND Year(DateReported)=" & Yearr & " ORDER BY DateReported ASC"
  
    Set rstGetExportData = dbs.OpenRecordset(strSQL)
    
    objActiveWkb.sheets(MonthName(reportarray(Q))).Select
    objActiveWkb.ActiveSheet.Cells(2, 1).CopyFromRecordset rstGetExportData

 

[georgedwilkinson]

I was unable to get ADO to fix this problem when I ran into it. This was one of the most painful experiences I ever had with Access.

Eventually, I had to create a new, intermediate table with a memo field. Then I was able to use TransferSpreadsheet and get more than 255 characters to transfer.

 

[QuietRiot]

can anyone confirm that?

Is there any other resolution other than switching to transferspreadsheet.

 

[QuietRiot]

anyone have a solution?

Thanks,

 

[georgedwilkinson]

See post 2. If you need more info, let me know.

 

[QuietRiot]

I can't use transfersheet due to the special format wanted. Anyone have any solutions?

 

[dkinley]

I've got this thing that I found.

In a nutshell (or module), it copies a spreadsheet (template) from one folder and pastes it in another (customized rename). It then (using DAO) copies each field into the designated column/row of the spreadsheet (designated tab) recursively (until EOF).

I like it because I design the spreadsheets prior to with headers, images so it looks all official like. Then all it does it copy the original somenametemplate.xls from the template directory and pastes it in an output directory for the user to fetch.

Is sound like something? If so, there is the link.

http://www.databasejournal.com/features/msaccess/article.php/3563671

-dK