Export to Excel using VBA - formatting trouble

[svjensen]

Using a small script (see below) I export data from my Access application to Excel.
This works fine.

The only problem is, that the data is exported as text, which I thus try to change. I have tested the script in excel and it works fine, but I cannot get it to work from Access.

The part of the script which fails is after:
'Trying to change the format of the data <----------------------

The error message is related to the line:
oSheet.Range(Cells(2, i), Cells(iNumRows, i)).Select

and seems to be related to the 'Cells' reference.

Error message:
Run-time error '1004':
Method 'Cells' of object '_Global' failed

My thought is, that I somehow need to tell it, that it is an an excel object, but have been unable to find out how...

/Soren

Function DumpSQLToExcel(strSQLStringToDump As String)
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim oApp As New Excel.Application
   Dim oBook As Excel.Workbook
   Dim oSheet As Excel.Worksheet
   Dim i As Integer
   Dim iNumCols As Integer, iNumRows As Integer
   Dim c As Range
 
   Set db = CurrentDb
   Set rs = db.OpenRecordset(strSQLStringToDump, dbOpenSnapshot)
 
   'Start a new workbook in Excel
   Set oBook = oApp.Workbooks.Add
   Set oSheet = oBook.Worksheets(2)
 
   'Add the field names in row 1
   iNumCols = rs.Fields.Count
   For i = 1 To iNumCols
       oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
   Next
 
   'Add the data starting at cell A2
   oSheet.Range("A2").CopyFromRecordset rs
   iNumRows = rs.RecordCount
 
   'Trying to change the format of the data <----------------------
   For i = 1 To iNumCols
       If IsNumeric(oSheet.Cells(1, i).Value) Then
           oSheet.Range(Cells(2, i), Cells(iNumRows, i)).Select
           For Each c In Selection
               If c.Value <> "" Then c.Value = CDbl(c.Value)
           Next c
       End If
   Next i
 
   'Format the header row as bold and autofit the columns
   With oSheet.Range("a1").Resize(1, iNumCols)
       .Font.Bold = True
       .EntireColumn.AutoFit
   End With
 
   oSheet.Range("A1").Select
   oSheet.Name = "Datasæt"
   Set oSheet = Nothing
 
   'Opretter fane med forudsætninger (aktive filtre)
   Set oSheet = oBook.Worksheets(1)
   oSheet.Name = "Forudsætninger"
 
   With oSheet.Range("A1")
       .Value = "Forudsætninger"
       .Font.Bold = True
       .Font.Size = 16
   End With
 
   With oSheet.Range("A3")
       .Value = "Følgende forudsætninger ligger til grund for datatrækket"
       .Font.Bold = True
   End With
 
   With oSheet.Range("A4")
       .Value = "Version:"
       .Offset(0, 1).Value = GetPPVersion()
   End With
 
   With oSheet.Range("A5")
       .Value = "BC status:"
       .Offset(0, 1).Value = GetStatusFilterFull()
   End With
 
   With oSheet.Range("A6")
       .Value = "BC type:"
       .Offset(0, 1).Value = GetTypeFilterFull()
   End With
 
   With oSheet.Range("A7")
       .Value = "Prisniveau:"
       .Offset(0, 1).Value = GetPNYear()
   End With
 
   With oSheet.Range("A8")
       .Value = "Beløbsangivelse:"
       .Offset(0, 1).Value = GetAmountFactorAbb()
   End With
 
   With oSheet.Range("A1")
       .EntireColumn.ColumnWidth = 20
   End With
 
   With oSheet.Range("B1")
       .EntireColumn.AutoFit
       .EntireColumn.HorizontalAlignment = xlLeft
   End With
 
   oApp.Visible = True
   oApp.UserControl = True
 
   'Close the Database and Recordset
   rs.Close
   db.Close
End Function

 

[nathansav]

You have referenced cells in the 2nd line, different to the first, so access is trying to use the cells

Correct
If IsNumeric(oSheet.Cells(1, i).Value) Then
Incorrect
oSheet.Range(Cells(2, i), Cells(iNumRows, i)).Select

Havent tested it, but think thats it.

Thanks

 

[svjensen]

No that is not the problem.
Access is supposed to test for numeric values in the first row (headers), since the years (2011, 2012, 2013, ...) are passed as numbers, whereas the corresponding values are passed as text (apparently due to the summation done ind the cross tab query).
So after finding a numeric value in the header row, access must convert the values below it to numbers.

And as mentioned it works in excel...

 

[svjensen]

I psoted my problem on MrExcel.com as well, which gave a solution:
http://www.mrexcel.com/forum/showthread.php?p=2600327#post2600327

 

[nathansav]

What i was getting at is, do you not need to reference the

oSheet.Range(Cells(2, i), Cells(iNumRows, i)).Select
as
oSheet.Range(oSheet.Cells(2, i), oSheet.Cells(iNumRows, i)).Select