0

I'm writing a macro that will populate an excel file with user inputs from active x controls in word. I've got almost everything working except that I keep getting an error message when I try and select cell A1 in the sheet that I want to use in the workbook. Here is the code:

    Workbooks.Open ("mypath\myfile.xlsm")
    Workbooks("myfile.xlsm").Activate
    Worksheets("sheet1").Select
    Range("A1").Select
    Do Until (IsEmpty(ActiveCell.Value))
    ActiveCell.Offset(1, 0).Select
    Loop
    ActiveCell.Value = n
    ActiveCell.Offset(0, 1).Value = a
    ActiveCell.Offset(0, 2).Value = b
    ActiveCell.Offset(0, 3).Value = c
    Columns("D:D").EntireColumn.AutoFit
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Workbooks("myfile.xlsm").Save
    Workbooks("myfile.xlsm").Close

The variables in this block of code are the values of the active x controls and are located much further up in the sub. This block of code is a small part of an if statement within the sub. Anyhow, when I take Range("A2").Select out of the code, it works just fine except for the fact that the information that I want to input does not go to the right spot (since it didn't select range A1 to begin with).

The error I get is type mismatch 4218.

Community
  • 1
  • 1
Nicolas Klebusits
  • 55
  • 10
  • Your `Do.. Until` loop.. is that to find the *first* empty cell in column A, or the cell below the *last* used cell in column A? – CLR Jul 06 '18 at 15:23
  • @CLR to find the last used cell in column a – Nicolas Klebusits Jul 06 '18 at 15:25
  • You don't provide enough information. Is this code running in Word or Excel? The way it looks, it's running in Excel because there are no application qualifiers, but if we can't be sure it's difficult to trouble-shoot. – Cindy Meister Jul 06 '18 at 15:40
  • @Cindy Meister the code is running in word. I added the excel object library in references. – Nicolas Klebusits Jul 06 '18 at 15:42
  • 1
    Then could you show us the code that instantiates Excel, please? Do you have some kind of `With` that you're not showing us? – Cindy Meister Jul 06 '18 at 15:45
  • @CindyMeister I believe the `Excel.Application` reference is implicit in this case (which is a problem). – Mathieu Guindon Jul 06 '18 at 15:46
  • FWIW you don't need to `.Select` anything at all. See [how to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1) – Mathieu Guindon Jul 06 '18 at 15:47
  • 1
    Exactly, @MathieuGuindon. But rather than guessing I'd like to see confirmation so that a useful answer can be composed that teaches people how to deal with the situation. – Cindy Meister Jul 06 '18 at 15:47

3 Answers3

1

Referencing the Excel object model gives you access to some global objects defined in that object model.

VBA resolves identifiers in this order:

  1. Current procedure
  2. Current module
  3. Current project
  4. VBA standard library
  5. Host application object model
  6. Any other referenced library, in the order they appear in the references dialog

So when you invoke Range meaning to be a call to the Excel object model, you actually invoke the same-name Range global member that's defined in the Word object model.

Note I say member and mean it: these are unqualified member calls to Global.Range. This is important, because a member implies an object, and since everything in the Excel object model (Word's too) has an Application property, then if you're not explicit about exactly what you're referring to, you might be implicitly creating an Excel.Application object, that you can't quite clean up properly. This usually translates into a "ghost" EXCEL.EXE process lingering in Task Manager well after your macro finishes running.

The trick is to make that reference explicit, and explicitly constrain its lifetime - a With block is perfect for this:

With New Excel.Application
    With .Workbooks.Open(path)
        With .Worksheets("Sheet1")
            lRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            .Cells(lRow, 1) = n
            .Cells(lRow, 2) = a
            .Cells(lRow, 3) = b
            .Cells(lRow, 4) = c
            .Columns("A:D").EntireColumn.AutoFit
        End With
        .Save
        .Close
    End With
    .Close
End With
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
0

I'm guessing as I don't usually run Excel from Word, but I think the problem might be related to everything being unqualified from Word.

If Workbooks.Open is working, then we can just hang everything related to that workbook on that..

Try the following code instead:

Dim myWkBk As Workbook, lRow As Long

Set myWkBk = Excel.Application.Workbooks.Open("mypath\myfile.xlsm")
With myWkBk.Sheets("sheet1")
    lRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
    .Cells(lRow, 1) = n
    .Cells(lRow, 2) = a
    .Cells(lRow, 3) = b
    .Cells(lRow, 4) = c
    .Columns("A:D").EntireColumn.AutoFit
End With
myWkBk.Save
myWkBk.Close
CLR
  • 11,284
  • 1
  • 11
  • 29
  • 1
    `Workbooks` should be qualified with an `Excel.Application` instance, lest there's an implicit reference to `Excel.Application` there, which likely results in a lingering EXCEL.EXE process in Task Manager after the macro completes. – Mathieu Guindon Jul 06 '18 at 15:43
  • 1
    No sure why this answer was downvoted. Unqualified `Range` member calls can't possibly resolve to `Excel.Global.Range` when hosted in Word: `Word.Global.Range` takes precedence. – Mathieu Guindon Jul 06 '18 at 15:43
  • Yeah, I thought it was odd that the OP stated that "it works just fine except for the fact...." - which made me think that `Workbooks.Open` implied Excel to Word, so by hanging everything off that qualified object, it ***should*** work. I shall fully qualify the `Workbooks.Open` in my answer. – CLR Jul 06 '18 at 15:48
  • hmm `Excel.Application.Workbooks.Open` still leaves a global object instance up in the air though: you really need `Set app = New Excel.Application`, or better, a `With New Excel.Application` block wrapping everything. – Mathieu Guindon Jul 06 '18 at 15:58
  • @MathieuGuindon - I'm more than happy for you to edit my answer, or copy/paste it into your own - I'm away from a desktop PC now until Monday am so unable to test anything before publishing. – CLR Jul 06 '18 at 16:03
0

I've got it figured out. @Cindy Meister I just needed to add an ActiveSheet. qualifier on the troubled line:

    Workbooks.Open ("H:\Second Rotation\OBI project\answersUsers.xlsm")
    Workbooks("answersUsers.xlsm").Activate
    Sheets("Answers Users").Select
    ActiveSheet.Range("A1").Select
    Do Until (IsEmpty(ActiveCell.Value))
    ActiveCell.Offset(1, 0).Select
    Loop
    ActiveCell.Value = n
    ActiveCell.Offset(0, 1).Value = cwid
    ActiveCell.Offset(0, 2).Value = mann
    ActiveCell.Offset(0, 3).Value = dept
    Columns("A:D").EntireColumn.AutoFit
    Workbooks("answersUsers.xlsm").Save
    Workbooks("answersUsers.xlsm").Close
    Dim myWkBk As Workbook, lRow As Long
Nicolas Klebusits
  • 55
  • 10