Run-time error '9': At a loss for where to go

[noslenwerd]

Hello,

I am running into an issue any time I try to run a macro with my excel "dashboard" when I have any other workbook open. That includes completely blank workbooks.

Below is the error, and also the last line of code below is where the error seems to happen. I even tried changing the name to the actual Sheet (Sheet4), and still no luck.

Option Explicit

Sub CreateWordDocuments()
Dim CustRow, CustCol, FinalCol As Long
Dim DocLoc, TagName, TagValue, TagRow, TemplName, FileName, TempFileName, ReviewLoc, WebsiteURL As String
Dim WordDoc As Word.Document, WordApp As Word.Application
Dim WordContent As Word.Range

Call Acc_Expand
Call ReqFields
Call TrimURL
Call populatetopics

    TempFileName = Sheets("DSSWorksheet").Range("comp")
    WebsiteURL = Sheets("DSSWorksheet").Range("C40")
    MsgBox "Please allow 1-2 minutes while the review for " & TempFileName & " is processed." & vbCrLf & vbCrLf & "The Dashboard will now minimize, and Word will open when the review is ready", vbExclamation
    ActiveWindow.WindowState = xlMinimized
    
    With Sheets("ReviewContent")

Thanks as always for your help.

 

[RoryA]

As Mark said, if you are referring to a sheet in the same workbook as the code, use ThisWorkbook.Sheets("ReviewContent") rather than just Sheets("ReviewContent")

 

[NdNoviceHlp]

I'm guessing your actual problem is where you dimmed your variables. For example

Dim DocLoc, TagName, TagValue, TagRow, TemplName, FileName, TempFileName, ReviewLoc, WebsiteURL As String

Putting "String" on the end doesn't dim all your variables as strings. Some of these variables seem like they must be strings or they will crash (ie filename etc) dependent upon how U use them. XL will do whatever if your not specific. Not sure if this helps or why your code crashes where it does? I'm confident that Joe, Mark and Rory will help U find a resolution to this but starting with the basics is usually good when U come across puzzling errors. HTH. Dave

 

[noslenwerd]

As Mark said, if you are referring to a sheet in the same workbook as the code, use ThisWorkbook.Sheets("ReviewContent") rather than just Sheets("ReviewContent")

This worked perfectly. Thank you!

Is there a similar command for Word? This process opens Word and runs macros there as well. Will 'ThisDocument' work in a similar fashion?

 

[noslenwerd]

I'm guessing your actual problem is where you dimmed your variables. For example

Dim DocLoc, TagName, TagValue, TagRow, TemplName, FileName, TempFileName, ReviewLoc, WebsiteURL As String

Would best practice be to do something like:

Dim DocLoc As String
Dim TagName As String
Dim TagValue As String

 

[MARK858]

Or

Dim DocLoc As String, TagName As String ,TagValue As String

'ThisDocument' work in a similar fashion?

Yes if you are in Word vba.

 

[RoryA]

If your code is in Excel but automating word, then you should assign the document to a variable when you open it and then refer to that variable.