0

I'm having a really hard time opening a workbook in VBA.

This line of code gives me a type mismatch. path is a String and I am 100% sure it is valid and I am able to do the .SaveAs with path without issues.

' Compile Error: Invalid qualifier on Workbooks
Set wbNew = Workbooks(path).Open

I have tried other permutations like this one, but I get Compile Error: Invalid qualifier on the Workbooks object itself. Am I missing a reference?

' Compile Error: Invalid qualifier on Workbooks
Workbooks.Open path

How do I open a workbook in VBA?

rlb.usa
  • 14,942
  • 16
  • 80
  • 128
  • Isn't the runtime error *subscript out of range* in the first snippet? – Mathieu Guindon Sep 07 '16 at 18:28
  • It's not a duplicate because the `Application.` part isn't a part of any SO question I could find, trust me, I spent a while looking at the one you linked and it wasn't obvious at all. – rlb.usa Sep 07 '16 at 18:43
  • 1
    Would you happen to have some variable or function named `Workbooks` in scope? The name clashing would explain it. – Mathieu Guindon Sep 07 '16 at 18:45
  • 2
    @rlb.usa - From within Excel, `Application.Workbooks` *is* `Workbooks`. You're just using the implicit global copy of the collection. – Comintern Sep 07 '16 at 18:46

2 Answers2

1
Set wbNew = Workbooks(path).Open

The path should be an argument to the Open method; you're passing it as an index to the Application.Workbooks collection - that's the collection of workbooks already opened, so path presumably wouldn't correspond to any already-opened workbook, hence the error.

So the correct and fully qualified call would be:

Dim wbNew As Excel.Workbook
Set wbNew = Application.Workbooks.Open(path)
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
-3

TRY THIS

Path = "C:\Users\USER\Desktop\test.xlsx"

Dim myShell As Object

Set myShell = CreateObject("WScript.Shell")

myShell.Run Path

irvin
  • 14
  • 3
  • 1
    Why don't _you_ try it and verify that it works? After all, answers are only supposed to be written if you 100% know that it's correct. I think you'll be surprised by the result... – byxor Sep 07 '16 at 18:49
  • Sorry, i didnt paste everything. Ill double check next time. – irvin Sep 07 '16 at 19:54
  • No problem. StackOverflow gets easier the more you do it. Soon you'll be full time ;) – byxor Sep 07 '16 at 19:56
  • It worked it just needs to change the path... I hope :S is my first post. It worked for me. – irvin Sep 07 '16 at 20:03