Results 1 to 3 of 3
  1. #1
    VicM is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2017
    Location
    US-PA & FL
    Posts
    49

    Can't get some Excel commands to work in Access sub after creating macro in Excel & porting 2 Access

    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

  2. #2
    VicM is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2017
    Location
    US-PA & FL
    Posts
    49
    Folks,

    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

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Thanks for the info.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Can Access work with Excel ?
    By _Chris_ in forum Access
    Replies: 2
    Last Post: 08-03-2016, 07:02 AM
  2. Replies: 3
    Last Post: 10-08-2014, 11:05 AM
  3. Access opens but commands dont work
    By tngirl in forum Access
    Replies: 2
    Last Post: 04-04-2014, 02:39 PM
  4. Sorting a Access field using Excel 2007 commands
    By Philosophaie in forum Access
    Replies: 2
    Last Post: 05-13-2013, 07:16 PM
  5. Import Excel into access does not work
    By hawg1 in forum Import/Export Data
    Replies: 1
    Last Post: 05-28-2010, 12:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums