Using 'Cells' in VBA in an Access application

[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

 

[Colin Legg]

Hi,

I haven't checked the rest of the code, but I can see the problem with that line: you must qualify those Cells properties in the same way you have qualified the Range property:

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

This is especially the case because you are automating Excel: if you use any Excel classes, they must be properly qualified to your Excel variables (oApp, oBook, oSheet etc) otherwise you will find that you get ghost Excel apps in the task manager.

Also note that it is usually not necessary to Select a range in VBA to work on it.

 

[RoryA]

Further to what Colin said, you also have an unqualified reference to Selection. That block could be written as:

   'Trying to change the format of the data <----------------------
 With oSheet
   For i = 1 To iNumCols
       If IsNumeric(.Cells(1, i).Value) Then
           For Each c In .Range(.Cells(2, i), .Cells(iNumRows, i))
               If c.Value <> "" Then c.Value = CDbl(c.Value)
           Next c
       End If
   Next i
  End With

 

[svjensen]

Excellent! That did the trick.

Thanks a lot both of you

/Soren