INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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.

CODE

Dim impfile 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
 impfile.Application.ActiveWorkbook.ActiveSheet.Range("AN2").Select
 impfile.Application.ActiveWorkbook.ActiveSheet.Range("AN2").Formula = "=IF(RC[-29]=""Male"",""M"",""F"")"
 impfile.Application.ActiveWorkbook.ActiveSheet.Selection.AutoFill Destination:=Range("AN2:AN1220"), Type:=xlFillDefault
 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

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

(OP)
Sorry I pressed Submit instead of Preview.

Any ideas?

Thanks,
Vic

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

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: impfile.Application.Visible=True,

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

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

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

combo

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

(OP)
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)

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close