Export to Excel 255 max Chars

[DevastatioN]

Hello, not sure exactly where to put this one..

Am using VBA code to open an instance of Excel, and copy data into a premade template. I have memo fields that are often over 255 chars, and when pasting it into Excel only 255 chars appear. I need a way to fix this.

Access 2003 -> Excel 2003

Code Sample:

'Input the database information into their correct Excel locations
With objActiveWkb.Worksheets(1)
.cells(2, 3) = rstProgram.Fields("ProgramID")
.cells(3, 3) = rstProgram.Fields("ProgramTitle")
.cells(4, 3) = rstProgram.Fields("Description")
.cells(5, 3) = rstProgram.Fields("Scope")
.cells(6, 3) = rstProgram.Fields("OPI")
End With

etc. I tried using a String variable instead, no luck. Any ideas?

 

[SOS]

Are you opening the table or a query as the recordset?

 

[DevastatioN]

Table, and this is the code that declares the recordsets:

Dim rstProgram As DAO.Recordset

'Set and Open the Queries and Recordsets
Set dbCurrent = CurrentDb
Dim rstProgramSQL As String
rstProgramSQL = "SELECT tblPrograms.ProgramID, tblPrograms.ProgramTitle, tblPrograms.ReportingRequirements, tblPrograms.Description, tblPrograms.Scope, tblPrograms.OPI, tblPrograms.[ReviewedAndCurrent?], Count(tblProjects.ProjectID) AS [Number of Projects] " & _
"FROM tblPrograms LEFT JOIN tblProjects ON tblPrograms.ProgramID = tblProjects.ProgramID " & _
"GROUP BY tblPrograms.ProgramID, tblPrograms.ProgramTitle, tblPrograms.ReportingRequirements, tblPrograms.Description, tblPrograms.Scope, tblPrograms.OPI, tblPrograms.[ReviewedAndCurrent?] HAVING (tblPrograms.ProgramID='" & strProgram & "');"

Set rstProgram = dbCurrent.OpenRecordSet(rstProgramSQL, dbOpenDynaset)

 

[SOS]

I think the problem is your GroupBy clause. Perhaps if you take the query you are building without the memo field and then a separate query for the memo field with the ID field and then join it back up, it will work. I believe that there are issues with GroupBy's with memo fields.

 

[DevastatioN]

Wow, I had no clue that a simple Group By clause could cause that.. you're correct, I ran a quick test on a second recordsource and it worked perfectly.

Thanks very much.

Now, do all queries that use an equation (Sum, Count, etc.) have to have a group by?

In reality all I want is the WHERE (instead of GROUP BY and HAVING).

 

[SOS]

Wow, I had no clue that a simple Group By clause could cause that.. you're correct, I ran a quick test on a second recordsource and it worked perfectly.

Thanks very much.

Now, do all queries that use an equation (Sum, Count, etc.) have to have a group by?

In reality all I want is the WHERE (instead of GROUP BY and HAVING).

If you are using aggregation then yes you will have the group by in there. So, you do a separate query for the numbers and then tie together a separate query for the memo fields.