Import Macro trying to gain acces to file that doesn't exist.

Hello.
This code has worked.

I get the following error:

"Run-time error '1004': "cmc4906.xls" File cannot be found.
Check the spelling of the file name and verify that the file
location is correct."

I am not trying to open a xls file. The path is clear and there are no file names with extensions in the path name (C:\Weekly\). I'm unclear of why the code thinks its looking for file cmc4906.xls and a xls file at that.

Sub Import()

Dim inputfile As Variant
Dim path As Variant

path = ("C:\Weeklys\")
inputfile = Dir("C:\Weeklys\")

Do While inputfile <> ""

Workbooks.OpenText Filename:=inputfile, Origin:=437, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 1), Array( _
3, 1), Array(4, 1), Array(5, 2), Array(6, 2), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 2)), _
TrailingMinusNumbers:=True

inputfile = Dir()
Loop

End Sub

Thank you for your consideration.

The path might be clear to you, but ...

I think your code is almost perfect. But, I think you want something like:
inputfile = Dir("C:\Weeklys\*.txt")

What I really think you want is:
inputfile = path & Dir(path & "*.txt")

Otherwise, Dir is going to return the first file it finds. Apparently, it worked before. But, I think that might have been pure luck.

Anyway, try one or both of above and see if that helps.

Thank You for your response.

The files do not have format extensions. I have tried adding txt extensions on them and it doesn't work. The code didn't work until I eliminated a file format after trying to make it work by adding the file format.

The folder "Weekly's"; contains only files that will be opened and processed.

The code does work but after I have open one of the files from the Weekly's folder and converted it manually. It is after I have manually opened one that it will process the entire folder. I'm trying to eleminate opening manually since I have hundreds of files to process on a weekly basis.

The files are ftp'd from a AS400.

It seems as if the code retains a processed file from the previous call on the macro that it trys to process when trying to run the macro at next use.

Any ideas are greatly appreciated. THANK YOU

You might be right about Dir retaining some "memory". I'll see what I can find about that ... I am sure there is a way to clear it (like, maybe "new filesearch").

Meanwhile, is the LACK of a file extension something you can use to exclude files? For example,

Do While inputfile <> ""

processFile = True
if left(inputFile, 4) like ".???" then processFile = False

if processFile Then
Workbooks.OpenText Filename:=inputfile, Origin:=437, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 1), Array( _
3, 1), Array(4, 1), Array(5, 2), Array(6, 2), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 2)), _
TrailingMinusNumbers:=True
End if
inputfile = Dir()
Loop

End Sub

Hello

Thank You MSP.

Still unsuccessful. I've been adding the suggested code. I get error messages that the code is unable to find ("C:\Weeklys"). Its there.

Maybe your suggestions were not applied correctly. Copied code below.

Sub Import()
'
' Import Macro
' Macro recorded 12/5/2006 by User
'
Dim inputfile As Variant
Dim path As Variant
Dim processFile As Variant

inputfile = ("C:\Weeklys\.???")


Do While inputfile <> ""

processFile = True
If Left(inputfile, 4) Like ".???" Then processFile = False
If processFile Then

Workbooks.OpenText Filename:=inputfile, Origin:=437, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 1), Array( _
3, 1), Array(4, 1), Array(5, 2), Array(6, 2), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 2)), _
TrailingMinusNumbers:=True
End If
inputfile = Dir()
Loop

Thanks again

Hi Jen,

My first thought was ... "oh my dear, where's the Dir?". My second thought was to write some code to illustrate what I had in mind.

What?!? You can't read my mind?

In the process found that I made a mistake, you didn't catch me, and then you compounded the error. Anyway, see below. I have tested it.

Please Login or Register  to view this content.

I put various files in the test directory, and it appropriately identified the ones to open and the ones not to open.

Hope this illustrates the point.

In a "Like" comparison, "*" is a "wildcard", which can represent any number or kind of string. "?" is a slightly less "wild" card, it represents exactly one character. "#" (not used here) is also wild, but it represents exactly one number.

So, in this case, instead of using Left(inputFile, 4), (which was wrong, by the way, it should have been Right(inputFile, 4) ... I make that mistake pretty often), I used "*.???" to represent exactly the same thing.

Hello Msp. Thank you for all the suggestions.

I apologize for the syntax errors. The missing asterisk and Dir.

I have tested the “if” statement before and after the code that does the processing of the files. (Opens them and formats them)

“Else” Before the process code (workbooks.open.) I still get an error unable to open cmc4906.xls . (No msgBox activated.) cmc4906.xls is not in the path/folder at all. It once was with testing. Cmc4906 is in the folder but it does not have an extension/format. None of the files in the folder will have an extension upon running the macro to import.

“Else” After the processing code the msgBox does display the file names in the folder. It first shows the first one in folder cmc4906, (No xls at this point. so this is correct.) then as I press the ok button in the message box, it will display each file name! Yeh!

So the Loop is working and the ability to see the folder is there. Your code suggestions work.

Below is my current code. I am now researching the “new filesearch” and its behaviour that you had suggested . It seems like the code is remembering prior processes. Old Filenames. That same file name (cmc4906.XLS) continues to show up and prevents the “workbooks.open” to do its job.
Haunting.

Sub Import()

Dim inputfile As Variant
Dim path As Variant
Dim strPath As Variant

strPath = ("C:\Weeklys\*.???")
inputfile = Dir(strPath)


Do While inputfile <> ""

If inputfile Like "*.???" Then

' MsgBox inputfile, vbInformation, " shows an error message box. Get error unable to open cmc4906.xls..

' Else
Workbooks.OpenText Filename:=inputfile, Origin:=437, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 1), Array( _
3, 1), Array(4, 1), Array(5, 2), Array(6, 2), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 2)), _
TrailingMinusNumbers:=True
Else
MsgBox inputfile, vbInformation, "shows file names in file" 'here; you can "ok" through msgBox each file and view filenames of all the files that I want to open and convert'

End If
inputfile = Dir()
Loop

End Sub

Thank You

Hello, MSP and inquiry's,

Just an Fyi. I changed all the names in the folder. Left them with no extensions. The code is assuming its looking for a file with an xls format.

I still get an error that its looking for cmc5906.xls (file name after change) and can't find it...
So its not remembering its old process it just assumes its looking for an extension. An xls extension.

I know VBA can do this. This code has worked. I was just lucky.

Any advice or ideas is greatly appreciated. Meanwhile, I'll continue to mine for more knowledge..

Thank YOU!

Hello msp.

Thank you for the response. I'm going to attempt to utilize it today..
You've been of enormous assistance to me. Greatly appreciative.

Just an FYI. Meanwhile I've been using this basic code. It works most of the time but not reliable.

Sub import1()
'
' import1 Macro
' Macro recorded 12/13/2006 by User
'
Dim inputfile As Variant
Dim path As Variant

path = ("C:\Weeklys\")
inputfile = Dir("C:\Weeklys\")

Do While inputfile <> ""
ChDir "C:\Weeklys"
Workbooks.OpenText Filename:=inputfile, Origin:=437, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 1), Array( _
3, 1), Array(4, 1), Array(5, 2), Array(6, 2), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 2), Array(16, 1)), _
TrailingMinusNumbers:=True
inputfile = Dir()
Loop

End Sub

Many Thank you's

Thanks. I should have thought of that as well.

I kind of confirms my theory. That the problem was the OpenText command was failing because inputfile was only a file name and not a complete path.

Using ChDir should fix the problem. The "not reliably" part of it is always troubling. I can tell you that the filesearch routine works for me VERY reliably.

But, to stick with your current program (and why not, if you can make it reliable, you already know it), you might make two changes.

First, replace "Path" with something else, like "myPath". The word Path has a meaning in Excel, and when you use the command

Please Login or Register  to view this content.

You are changing the path where your autorecovery files are stored.

Second, change the line below:

Please Login or Register  to view this content.

This should make it more reliable.