Enter formula in Excel cell from Access DB code, copy down, then replace original col with mod data

[VicM]

Here's another situation I'm unable to resolve.

In the course of coding a subroutine in an Access 2010 DB, I'm trying to manipulate an Excel spreadsheet so I can extract the data I need for the DB.

I first opened an Excel spreadsheet and began recording a macro to enter a formula into a cell which references other cells, then copy that formula down a column, and finally replacing the original column with the modified data.

When I stopped the recording, I attempted to port that code to the Access DB code window. I ran into at least one problem. Since I didn't get past that one, I don't yet know if others will crop up.

Here's a code snippet. I want to extract the gender info and convert it to just one letter, M or F. The spreadsheet contains the full identifiers: Male and Female. Then I want to copy the modified data to the original column as paste value only. The Access table I need to enter this data into defines the gender column as text with one character.
The gender data in the spreadsheet is located in column K which has a header. I want to work in an unused column in the spreadsheet. In this case it's column AN.

Dim impfile as object
 Set impfile = CreateObject("Excel.Sheet")
 impfile.Application.Workbooks.Open filepathXL, 0, False [COLOR=#4E9A06]'Where filepathXL is the full path and name of the Excel file[/color]
 impfile.Application.ActiveWorkbook.ActiveSheet.Range("AN2").Select
 impfile.Application.ActiveWorkbook.ActiveSheet.Range("AN2").Formula = "=IF(RC[-29]=""Male"",""M"",""F"")"
 [COLOR=#EF2929]impfile.Application.ActiveWorkbook.ActiveSheet.Selection.AutoFill Destination:=Range("AN2:AN1220"), Type:=xlFillDefault[/color]
 impfile.Application.ActiveWorkbook.ActiveSheet.SelectionRange("AN2:AN1220").Select
 impfile.Application.ActiveWorkbook.ActiveSheet.SelectionSelection.Copy
 impfile.Application.ActiveWorkbook.ActiveSheet.SelectionRange("K2").Select
 impfile.Application.ActiveWorkbook.ActiveSheet.SelectionSelection.PasteSpecial Paste:=xlPasteValues, _
           Operation:=xlNone,SkipBlanks:=False, Transpose:=False
 impfile.Application.ActiveWorkbook.ActiveSheet.SelectionRange("AN2:AN1220").Select
 impfile.Application.ActiveWorkbook.ActiveSheet.SelectionSelection.Delete

impfile.Application.ActiveWorkbook.Save
impfile.Application.Quit

End Sub

The code fails at the line colored red. The error message is:

Run-time error '1004':
Method 'Range' of object '_Global' failed​

 

[VicM]

Sorry I pressed Submit instead of Preview.

Any ideas?

Thanks,
Vic

 

[combo]

1. Do you have reference to Excel library? If not, vba in access does not recognise xlFillDefault and assumes it as 0 (in fact, xlFillDefault=0), the same for other named excel constants.

2. At least in design time make excel visible, you will see what is going on and will be able to close instance if the code breaks: [tt]impfile.Application.Visible=True[/tt],

3. Selection is a property of excel application or window objects, you apply it to sheet, so the error. In:
[tt]impfile.Application.ActiveWorkbook.ActiveSheet.Selection.AutoFill Destination:=Range("AN2:AN1220"), Type:=xlFillDefault[/tt]
object types are:
[tt]Workbook(?).Application.Workbook.Worksheet.Selection.[/tt] you apply it to worksheet,

4. Excel has no [tt]SelectionRange[/tt] and [tt]SelectionSelection[/tt]. There is RangeSelection that refers to window,

5. [tt]Delete[/tt] of range means removing the range and shifting cells. This may affest data below. To clear contents and formats only use [tt]Clear[/tt] instead.


combo

 

[VicM]

combo,

Thanks for your response. I do have reference libraries installed. (Hopefully all the right ones!!)

After many hours and trials I was able to get the code to function as I wanted it to. Here's what it looks like now:
(iRow is extracted from the UsedRange.Rows.Count property in Excel)

Dim impfile as object, XSS as object

 Set impfile = CreateObject("Excel.Sheet")

 impfile.Application.Workbooks.Open filepathXL, 0, False 'Where filepathXL is the full path and name of the Excel file

    set XSS = impfile.Application.ActiveWorkbook.ActiveSheet

    rngOrigRows = "K2:K" & iRow
    XSS.Range(rngWkRows).Formula = "=IF(RC[-29]=""Male"",""M"",""F"")"
    XSS.Range(rngWkRows).Copy
    XSS.Range(rngOrigRows).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    XSS.Range(rngWkRows).Delete
impfile.Application.ActiveWorkbook.Save
impfile.Application.Quit

End Sub

So basically recording a macro in Excel does not seamlessly port over to Access VBA. There are many changes that need to be accounted for.
Once I had the sequence established, I was able to use the format for many other changes.

Vic