Hi,
I'm maintaining a DB for client that requires monthly updates to a demographic table. The data is submitted to my client via Excel spreadsheets. Initially when there was only one source for the data, I had the subroutine functioning smoothy. But recently, because of a split-off, there are two source spreadsheets for the data. Unfortunately the new source's format is drastically different from the first source. So I'm trying to manipulate the second spreadsheet source to keep it in line with the established import routine.
In the Access subroutine one of the many reformats of the data is changing the gender column from the complete identifier (Male/Female) to just one character (M/F).
What I did was first open the spreadsheet and record a macro to perform this function. Then I copied the macro code to my Access subroutine. Initially I had many errors, but made some changes and got some of the code to work. However, I'm stuck on one line and can't figure out how to change it to work.
First here is the macro that got generated in Excel:
Code:
Range("AN2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-29]=""Male"",""M"",""F"")"
Selection.AutoFill Destination:=Range("AN2:AN1220"), Type:=xlFillDefault
Range("AN2:AN1220").Select
Selection.Copy
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Obviously I had to append those code lines to an object. So this is what it looked like after I added the object.
Code:
impfile.Application.ActiveWorkbook.ActiveSheet.Range("AN2").Select
impfile.Application.ActiveWorkbook.ActiveSheet.ActiveCell.FormulaR1C1 = "=IF(RC[-29]=""Male"",""M"",""F"")" 'First error
impfile.Application.ActiveWorkbook.ActiveSheet.Selection.AutoFill Destination:=Range("AN2:AN1220"), Type:=xlFillDefault 'Second error
impfile.Application.ActiveWorkbook.ActiveSheet.Range("AN2:AN1220").Select
impfile.Application.ActiveWorkbook.ActiveSheet.Selection.Copy
impfile.Application.ActiveWorkbook.ActiveSheet.Range("K2").Select
impfile.Application.ActiveWorkbook.ActiveSheet.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
The first error I got when running this code was at the line indicated in green (Object doesn't support this property or method). So I removed the R1C1 from the line and got same error. Then I changed ActiveCell to Selection. Same error. Changed Selection to Range("AN2"). That worked! So the new line reads:
Code:
impfile.Application.ActiveWorkbook.ActiveSheet.Range("AN2").Formula = "=IF(RC[-29]=""Male"",""M"",""F"")"
Then it failed on the very next line. So I changed Selection to Range("AN2:AN1220"). Still failed with msg: Method 'Range' of object '_Global' failed
I can't seem to get past this error. If I try copying the AN2 cell, then select AN3:AN1220 and try pasting, it only pastes into AN3. So it doesn't Autofill.
Keep in mind I could probably have the client perform the changes to the spreadsheet using a macro before I import it to Access. But these users are strictly: Give me one button to push. LOL So I need to code as much of the work in the background as possible.
Hope someone has insights.
Thanks,
Vic