up vote 6 down vote favorite
2
share [g+] share [fb]

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.

link|improve this question
96% accept rate
1  
if 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 May 28 '11 at 1:15
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 May 31 '11 at 20:11
1  
not 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 May 31 '11 at 20:51
No I didn't intend the doubles. Corrected. Thank you. So the 65001 value for Origin indicates UTF-8 encoding (en.wikipedia.org/wiki/Code_page). I cannot find the meaning of -535 or 535 which were in the original macro. – music2myear May 31 '11 at 22:38
1  
If 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 Jun 1 '11 at 1:32
show 1 more comment
feedback

2 Answers

up vote 2 down vote

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:

link|improve this answer
1  
this is a great reminder, related to the path of the file and worth checking. Hope this is it – datatoo Jun 4 '11 at 3:53
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 Jun 6 '11 at 15:03
myDir is a directory path (eg. "E:\Network Folder\Diff Folder\Stuf"). – music2myear Jun 6 '11 at 15:05
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 Jun 7 '11 at 21:40
feedback
up vote 1 down vote accepted

@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.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.