VBA Runtime error 1004: Method 'OpenText' of object 'Workbooks' failed

6

2

After an upgrade to Excel 2010 (from 2003), a script used for standardizing data from Access import has stopped working. It returns an error: "Run-time error '1004': Method 'OpenText' of object 'Workbooks' failed".

Prior to the OpenText portion, the macro searches a folder for the most recent file by DateCreated and then returns the filename and path.

The relevant code is:

Workbooks.OpenText FileName:=myDir & "\" & strFilename, _
    Origin:=-535, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
    , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
    Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
    Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15 _
    , 2), Array(16, 3), Array(17, 3), Array(18, 3), Array(19, 3), Array(20, 2), Array(21, 2), _
    Array(22, 1), Array(23, 2), Array(24, 2), Array(25, 3), Array(26, 2), Array(27, 2), Array( _
    28, 2)), TrailingMinusNumbers:=True

Any ideas what may be hanging Excel 2010 up?

The file is still a .xls file, and is running in Compatibility Mode, which I have not had issues with in any other macro yet.

UPDATED after recording new macro and comparing w/ old:

The new macro one difference, the Origin argument.

In the old macro:

Origin:=-535

The new macro:

Origin:=65001

Reading up on Origin, this appears to be the source of the file to be imported, and it can be omitted.

What importance would Origin have on the import? The DataType is already specified. Does Origin control the encoding?

Removing Origin from the original Macro, the same error is returned. Changing the Origin value to 65001 still returns the same error.

music2myear

Posted 2011-05-27T22:20:20.020

Reputation: 34 957

1if there actually is a filename, have you tried opening the file and recording a macro and then comparing that to your macro to see if there are differences? It may uncover something – datatoo – 2011-05-28T01:15:24.183

That's how I created this portion of the macro, initially. However, that is a good troubleshooting step anyways and so I'll do that and report back. Thank you. – music2myear – 2011-05-31T20:11:38.567

1not sure I know on this, but the relevant article says "If this argument is omitted, the method uses the current setting of the File Origin option in the Text Import Wizard." It is optional, but may be supplying the wrong codepage for your needs, based on a previous wizard run. also did you intend the double :: ? – datatoo – 2011-05-31T20:51:13.117

No I didn't intend the doubles. Corrected. Thank you. So the 65001 value for Origin indicates UTF-8 encoding (http://en.wikipedia.org/wiki/Code_page). I cannot find the meaning of -535 or 535 which were in the original macro.

– music2myear – 2011-05-31T22:38:04.740

1If it works when you record a macro, and select a file, I wonder if your file path or name are issues. Can you step through and see that is correctly assigning? noticed the same thing about -535 and so I wonder why it recorded that. – datatoo – 2011-06-01T01:32:51.633

I'm trying to test that, playing with the variables to have them display the path they'll be pulling prior to pulling. – music2myear – 2011-06-01T13:54:39.657

Answers

1

@datatoo, you pointed me in the right direction. It was a variable/path issue. Somehow, I'd started using strFullFile as a string to take the full file path string. However, I'd also simply modified strFileName, prepending the myDir string to create the full file path. Removing changing the appropriate references to strFullFile to strFileName resolved the issue.

music2myear

Posted 2011-05-27T22:20:20.020

Reputation: 34 957

2

Check the path:

UserProfileFolder = Environ("UserProfile")

and

Workbooks.OpenText Filename:

In Office 2003 the environment path of XP was:

%systemDrive%\Documents and Settings\{user}\My Documents

For Vista and Windows 7 it is:

%systemdrive%\users\Documents

where %systemdrive% is usually C:

Tony Stewart Sunnyskyguy EE75

Posted 2011-05-27T22:20:20.020

Reputation: 1 582

1this is a great reminder, related to the path of the file and worth checking. Hope this is it – datatoo – 2011-06-04T03:53:23.213

I haven't checked this yet. However, the macro and the reference will never touch the user profile or system drive or other standard local references. The macro and the found file are both in specific locations on a network drive. The macro searches a set directory for the most recent file and then runs processes on that file after confirming it's the one I want to process. – music2myear – 2011-06-06T15:03:36.063

myDir is a directory path (eg. "E:\Network Folder\Diff Folder\Stuf"). – music2myear – 2011-06-06T15:05:00.260

The problem with this is it is fundamentally based on the assumption the file is local, when that is not the case in this situation. – music2myear – 2011-06-07T21:40:54.110

Asked: 2011-05-27T22:20:20.020

Viewed: 7 302 times

Active: 2011-07-13T15:00:45.547