#VALUE! When Exporting from Access to Excel 1

[mikeH321]

I'm using Access 97 and Word 97 and am using the following code to export records from from Access to Excel:

Sub ecSPNEM()
On Error GoTo ErrHandler
   
   ' Excel object variables
   Dim appExcel As Excel.Application
   Dim wbk As Excel.Workbook
   Dim wks As Excel.Worksheet
   Dim sTemplate As String
   Dim sTempFile As String
   Dim sOutput As String
   
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim sSQL As String
   Dim lRecords As Long
   Dim iRow As Integer
   Dim iCol As Integer
   Dim iFld As Integer
   Dim highLight As Boolean
      
   'CONSTANTS
   Const aTab As Byte = 1
   Const aStartRow As Byte = 6
   Const aStartColumn As Byte = 1

   ' set to break on all errors
   Application.SetOption "Error Trapping", 0
   
   ' start with a clean file built from the template file
   sTemplate = "S:\HWYREPORTS\COL\Accounts\S\Template File for Exception Report\TSP News Exceptions Template.xls"
   
   If formdate("S", 8) = formdate("E", 8) Then
   
        sOutput = "S:\HWYREPORTS\COL\Accounts\S\SPNewsprint\SP News Exceptions " & Format(fdate(formdate("S", 8)), "mm-dd-yy") & ".xls"
   
        Else
   
        sOutput = "S:\HWYREPORTS\COL\Accounts\S\SPNewsprint\SP News Exceptions " & Format(fdate(formdate("S", 8)), "mm-dd-yy") & " through " & Format(fdate(formdate("E", 8)), "mm-dd-yy") & ".xls"
   
   End If
      
   If Dir(sOutput) <> "" Then Kill sOutput
   FileCopy sTemplate, sOutput
      
   ' Create the Excel Applicaiton, Workbook and Worksheet and Database object
   Set appExcel = New Excel.Application
   Set wbk = appExcel.Workbooks.Open(sOutput)
   Set dbs = CurrentDb
   sSQL = "select * from qryExport"
   Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
      
   ' ADDING COLUMN HEADERS TO EXCEL FILE
   
   Set wks = appExcel.Worksheets(aTab)
   iCol = aStartColumn
   iRow = (aStartRow - 1)
   
   If Not rst.BOF Then rst.MoveFirst
   
      iFld = 0
      lRecords = lRecords + 1
      
      For iCol = aStartColumn To aStartColumn + (rst.Fields.Count - 1)
            
            wks.Cells(iRow, iCol) = rst.Fields(iFld).Name
            wks.Cells(iRow, iCol).Interior.ColorIndex = 1
            wks.Cells(iRow, iCol).Font.ColorIndex = 2
            wks.Cells(iRow, iCol).Font.Bold = True
                        
            iFld = iFld + 1
        
                Next
              
                    iRow = iRow + 1
                    rst.MoveNext

   
   ' ADDING INFO TO EXCEL FILE
   iCol = aStartColumn
   iRow = aStartRow
   highLight = False
   
   If Not rst.BOF Then rst.MoveFirst
   Do Until rst.EOF
      iFld = 0
      lRecords = lRecords + 1
      
      For iCol = aStartColumn To aStartColumn + (rst.Fields.Count - 1)
            
            wks.Cells(iRow, iCol) = rst.Fields(iFld)
                       
            If highLight = True Then
            
                wks.Cells(iRow, iCol).Interior.ColorIndex = 15
            
            End If
                            
            iFld = iFld + 1
        
                Next
              
                    iRow = iRow + 1
                    rst.MoveNext
                    
                    If highLight = False Then
                    
                        highLight = True
                        
                            Else
                                
                                highLight = False
                    End If
   
   Loop

'AUTOFITTING COLUMNS

wks.Columns("A:F").EntireColumn.AutoFit
wks.Columns("H:R").EntireColumn.AutoFit

'CLOSING AND SAVING NEW FILES
   
   With appExcel
        With .ActiveWorkbook
                .Save
                    .Close
        End With
            .Quit
    End With
    
   On Error Resume Next
   Set wks = Nothing
   Set wbk = Nothing
   Set appExcel = Nothing
   Set rst = Nothing
   Set dbs = Nothing
   
   'Call AutoEmailAll("SPNEM - tblDistList", "Attached is the SP News Exception Report.  If the report is blank, there were no exceptions entered.", "SP News Exception Memo Report", sOutput)
   
ExitProcedure:
    Exit Sub

ErrHandler:
    Select Case Err.Number
        Case Else
            Call UnexpectedError(Err.Number, "ecSPNEM:  " _
                    & Err.Description, Err.Source, _
                    Err.HelpFile, Err.HelpContext)
            Resume ExitProcedure
            Resume
    End Select
   
   
   End Sub

Now the problem is that certain cells are showing up with "#VALUE!" in them instead of the actual value in the recordset. I'm relativly certain it has to do with the length of the value in field that is being placed into Excel but I can't seem to find any information on how to correct this. The two times I have seen this happen now both have character counts (including spaces) of just shy of 500.

 

[ClulessChris]

Try,

Recording the macro in Excel of you using the colum auto resize. This should give you a hint of how to do the same with VB.
There have been many times when recording a Macro have helped me in this way.

good luck

Everybody body is somebodys Nutter.

 

[TysonLPrice]

Stick a "'" in front of it. Like

"'" & rst.Fields(iFld).Name

There are probably characters in the string that make Excel think it is a formula.

 

[mikeH321]

Thanks Tyson, that partially solved my problem. Instead of getting the value error I'm now getting an entire cell filled with "############". When I click on the cell I can actually see the memo that should be there in the formula bar but when I click off it is still just a bunch of # signs. Any ideas?

 

[ClulessChris]

Sorry I should have read the routine. I missed that you already had Autofit set for the column.

Everybody body is somebodys Nutter.

 

[TysonLPrice]

Try changing the format from text to general or visa versa, I can't remember which

Selection.NumberFormat = "@" (This text)
Selection.NumberFormat = "General"

Try it on a single cell in your spread sheet first.

 

[mikeH321]

That was my first thought too but it did not work. Still just seeing the #'s

 

[TysonLPrice]

I didn't see where you are using word wrap in your code. Basically I think it's a sizing issue. Try wordwrap:

I think this was one I had that issue with:

With xl.Selection
.Font.Name = strFontName
.Font.Size = lngFontSize
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
End With

 

[mikeH321]

Thanks Tyson, I changed my code a bit and opted to just create a new excel sheet rather can copying it from a "template" file. Once I did that and went through and set the word wrap and cell format it worked great.