VBA Type missmatch

Question

I have wrote some VBA code which I was fairly happy with. It went through a list on a worksheet, switched to another and set a variable (and thus changed some graphs) and then opened word, copied in the graphs to various bookmarks and saved the document as the variable name.

It worked like a charm and I was a happy boy (saved a good week and a bit of work for someone). I have not touched it since - or the worksheets for that matter - opened it today and it is giving me a type missmatch on the first lot. I would really love some advice as it has left me scratching my head.

Public X As Integer
Public Y As String
Sub Macro2()


    'Set up variables that are required
    Y = ""
    LoopCounter = 2


    Do Until Y = "STOP"

        'Grab the value from a list
        Sheets("CPD data 13-14").Select
        Range("A" & LoopCounter).Select
        Y = Range("A" & LoopCounter).Value

        'Change the chart values
        Sheets("Pretty Display (2)").Select
        Range("A1").Value = Y

        'Open word template
        Set wordapp = CreateObject("word.Application")
        wordapp.documents.Open "LOCATION"
        wordapp.Visible = True
        wordapp.Activate

        wordapp.ActiveDocument.Bookmarks("InstitutionName").Range = Y
        wordapp.ActiveDocument.Bookmarks("Graph1").Range = ActiveSheet.ChartObjects("Chart 3")


        'Close document
        Mystring = Replace(Y, " ", "")
        wordapp.ActiveDocument.SaveAs Filename:="LOCATION" & Mystring & ".docx"
        wordapp.Quit
        Set wordapp = Nothing


        'Increase count and loop
        LoopCounter = LoopCounter + 1
    Loop

The error hits on the following line:

            wordapp.ActiveDocument.Bookmarks("Graph1").Range = ActiveSheet.ChartObjects("Chart 3")

EDIT

As suggested I have updated my code not to use select so it now reads:

Set ws = Sheets("CPD data 13-14")
Set pd = Sheets("Pretty Display (2)")

'Set up variables that are required
Y = ""
LoopCounter = 2


Do Until Y = "STOP"

    'Grab the value from a list
    Y = ws.Range("A" & LoopCounter).Value

    'Change the chart values

    pd.Range("A1").Value = Y

    'Open word template
    Set wordapp = CreateObject("word.Application")
    wordapp.documents.Open "LOCATION"
    wordapp.Visible = True
    wordapp.Activate

    wordapp.ActiveDocument.Bookmarks("InstitutionName").Range = Y
    wordapp.ActiveDocument.Bookmarks("Graph1").Range = pd.ChartObjects("Chart 3")


    'Close document
    Mystring = Replace(Y, " ", "")
    wordapp.ActiveDocument.SaveAs Filename:="LOCATION" & Mystring & ".docx"
    wordapp.Quit
    Set wordapp = Nothing


    'Increase count and loop
    LoopCounter = LoopCounter + 1
Loop

I still get the same runtime error at the same point.

Hi User, Do the bookmarks "Graph1" or "Chart 3" exists? its very hard to error search this code. I tried to use your code and recreate the tabs in the excelfile also the docx file. Thank you in advance — XsiSecOfficial, Feb 17 '16 at 13:05
Please review [How to Avoid Select in VBA](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) then qualify all your sheet objects and work **directly** with them. Most likely the `ActiveSheet` is not what you think it is and thus does not have the `ChartObject`. As an added benefit, reviewing the link I provided will save you tons of future coding bugs :) — Scott Holtzman, Feb 17 '16 at 13:30
Exact error is code 13 type missmatch. Thanks for the comments. I understand re: Avoiding select however the sheet "Pretty Display (2)" is most definitely the active sheet and this code did work previously. Graph1 is a bookmark that does exist and chart 3 is the name of a chart that exists within that spreadsheet. Thanks for the help, greatly appreciated. — user958551, Feb 17 '16 at 13:42
Variable Y would contain the name of an organisation, for the sake of argument let's say that could contain "Game Warehouse". — user958551, Feb 17 '16 at 15:11

score 0 · Answer 1 · answered Feb 17 '16 at 14:18

0

My advice is to set the Explicit flag and try to decompile the code. Any variables that you didn't dimension will throw an error. This is a good time to dimension the variable and type the data appropriately.

If that doens't throw an error, which it should since you have at least one variable LoopCounter that isn't dimensioned and could therefore cause data type errors then try changing Public X As Integer to Public X As Long so as to avoid values beyond the limit of the Integer data type.

.Activate is sometimes necessary even when using .Select from my experience. Selecting a worksheet should make it the active worksheet, but that's not always the case.

answered Feb 17 '16 at 14:18

Sorcefyre

204
2
2

Sorry for the dumb question, explicit flag? The X will not be going above the integer limit however I have now set it as Long. I have also now specifically Activated the sheet that it is pulling from, still getting the same error. – user958551 Feb 17 '16 at 15:13
Option Explicit at the top of your module (before any subroutines or functions) is a flag that enforces that all variables be dimensioned before being used (e.g., Dim X As Long has to exist before you can use X anywhere in your sub). – Sorcefyre Feb 17 '16 at 15:22
The only other thing I can think of would be to step through the code line by line (F8 to begin debugging and step line-by-line) and see where the error is being thrown. – Sorcefyre Feb 17 '16 at 15:24
Ok, I have set explicit and set all types (as long as wordapp should be an object) I am still getting the type mismatch on the same line of code. I might just throw the computer out of the window at this point. – user958551 Feb 17 '16 at 15:48

score 0 · Accepted Answer · answered Feb 17 '16 at 15:42

try this

Option Explicit

Public X As Integer
Public Y As String

Sub Macro2()

Dim wordApp As Object
Dim LoopCounter As Integer
Dim Mystring As String
Dim ws As Worksheet, pd As Worksheet

Set ws = Sheets("CPD data 13-14")
Set pd = Sheets("Pretty Display (2)")

'Set up variables that are required
Y = ""
LoopCounter = 2

' open one Word session for all the documents to be processed
Set wordApp = CreateObject("word.Application")

Do Until Y = "STOP"

    'Grab the value from a list
    Y = ws.Range("A" & LoopCounter).Value

    With pd
        .Range("A1").Value = Y 'Change the chart values
        .ChartObjects("Chart 3").Copy ' Copy the chart
    End With

    'act on Word application
    With wordApp
        'open word template
        .documents.Open "LOCATION"
        .Visible = True

        ' paste into bookmarks, "save as" document and close it
        With .ActiveDocument
            .Bookmarks("InstitutionName").Range = Y
            .Bookmarks("Graph1").Range.PasteSpecial

            Mystring = Replace(Y, " ", "")
            .SaveAs Filename:="LOCATION" & Mystring & ".docx"
            .Close
        End With
    End With

    'Increase count and loop
    LoopCounter = LoopCounter + 1
Loop

'Close Word
wordApp.Quit
Set wordApp = Nothing

End Sub

I couldn't have a word "Range" object directly set to an Excel "Chart" object

So I had to copy the chart and use "PasteSpecial" method of the Word "Range" object

Furthemore I worked with one Word session only, which'd result in a faster job

Finally I also made some "comsetics" to make the code more readable/maintanable

just as a suggestion: I'd always use "Option Explicit" statement. that'll force you some extra work to explicitly declare each and every variable you use, but that will also give much more control over your work and result in less debbugging issues, thus saving time at the end

This worked great, thank you. I still am not sure why it worked a day before and doesn't now, the pasting worked like a charm and I picked up a few tips to clean up my code from this - great answer! — user958551, Feb 17 '16 at 16:53

VBA Type missmatch

2 Answers2