MS-Excel VB Open Workbook

July 22, 2009 at 07:53:57
Specs: Microsoft Windows XP Professional, 2.399 GHz / 2047 MB

I am using the following syntax to open a workbook:
Application.Workbooks.Open p_file

When running this macro I get an error 4001 (Open method failed).

If I then go into debugging modus and press F8 to continue I get no further error and the macro continues to run ok.

Any ideas?

George


See More: MS-Excel VB Open Workbook

Report •


#1
July 22, 2009 at 10:03:10

Not without seeing more of the code; the only error I've seen for Workbooks.Open is error 1004.

Report •

#2
July 22, 2009 at 10:15:38

You're right, of course, Error number is 1004.

What I'm doing is working in one excel-sheet, that wants to cut-and-paste a line to a second sheet.
The second sheet is not open at the time of execution.
Here the syntax surrounding the problem:
lin = rowx & ":" & rowx
Rows(lin).Select
Selection.Cut

p_file = "C:\Dokuments\JLB\Web-Site\Members_None.xls"
Application.Workbooks.Open p_file


Sheets("Members").Select
Range("a1").Select
maxl = ActiveCell.Value + 2


Report •

#3
July 22, 2009 at 12:26:16

Step 1: Make sure "C:\Dokuments\JLB\Web-Site\Members_None.xls" exists. I assume "Dokuments" should be "Documents"
Step 2: Check the file's security to make sure you have the permissions to open it
Step 3: Make sure Excel can open the file outside of VBA.

Report •

Related Solutions

#4
July 22, 2009 at 23:49:15

I have no problem "opening" the document.
The calling macro runs up to the "open" command and then stops with error 1004. If I then press F8 it continues without reporting any further error..
This is what bothers me... Why does it stop in the first place, only to continue on F8?

Report •

#5
July 23, 2009 at 05:48:04

I have no problem "opening" the document.
If "Dokuments" isn't a typo, then I assume you're using the German version of Windows?

Why does it stop in the first place, only to continue on F8?
It stops because it can't open the workbook, "C:\Dokuments\JLB\Web-Site\Members_None.xls." If it does open the workbook when you press F8, then something was blocking Excel from opening the workbook, but the file lock is released by the time of your manual intervention. Does some other macro open/close the workbook? Does this macro run just before this one?


Report •

#6
July 23, 2009 at 06:55:30

There IS another macro that opens the file, however it runs separately.
It uses the same syntax, but has no problems.
Very strange!!

Report •

#7
July 23, 2009 at 12:00:35

Very strange indeed. This method is a bad hack, but it might get the script working again:
On Error Resume Next
Dim i As Integer
For i = 1 To 1000
  Err.Clear
  Application.Workbooks.Open "C:\Dokuments\JLB\Web-Site\Members_None.xls"
  If Err.Number = 0 Then Exit For
Next 'i
On Error GoTo 0


Report •

#8
July 24, 2009 at 00:16:54

Hey! IT WORKED!!

Thank's a lot fo that one!
Not a great leap forward for mankind, but at least I can sleep in peace again.

George


Report •


Ask Question