TRYING TO OPEN "ANY" EXCEL FILE in given folder

[Hamyatta]

-- I am using the following string to open "ANY" excel file in a specific folder..

Workbooks.Open Filename:= _
"C:\Excel Data Extract\AnyFile.xls"

-- Would it be better to use this one?
"C:\Excel Data Extract\ &''"& '.xls'"

Any ideas..?? THANKS!!!!

 

[fumei]

I don't understand. What do you mean "any"?

Workbooks.Open Filename:= _
        "C:\Excel Data Extract\AnyFile.xls"

Look at the code. The filename is a string. A string. The file has to actually exist, otherwise there is an error.

Look up Open in Help. Workbooks.Open opens the name of the file to be opened. Not "any" file, but a specific file.

Gerry
My paintings and sculpture

 

[Hamyatta]

Thanks Fumei, I am using the following code to first SEARCH, then OPEN all excel files within a given folder..

Here is the code I have. Any help sincerely appreciated with this....
--------------------------------------
Sub GetFileIII()
Dim lngCount As Long
With Application.FileSearch
.NewSearch
.FileTypes.Add msoFileTypeExcelWorkbooks
MsgBox "You are about to search for " & .FileTypes.Count & _
" file types."
C:\ drive.
.LookIn = "C:\AMY\Arbeit\Excel Data Extract"
.SearchSubFolders = False
If .Execute <> 0 Then
MsgBox "Files found: " & .FoundFiles.Count

For lngCount = 1 To .FoundFiles.Count
Workbooks.Open (.FoundFiles.Item(lngCount))

If MsgBox(.FoundFiles.Item(lngCount), vbOKCancel, "Found files") = vbCancel Then

lngCount = .FoundFiles.Count
End If
Next lngCount
Else
MsgBox "No files found."
End If
End With
End Sub
--------------------------------------

Any ideas..?? THANKS!!!!
HAMMYATTA

 

[JerryKlmns]

Add this line before .Execute
.FileType = 4 'msoFileTypeExcelWorkbooks

 

[JerryKlmns]

Plus what Skp's says in the other same post of yours
thread707-1282484

 

[fumei]

Hi Jerry. Has that thread been removed? The link does not go anywhere. So I am not sure what Skip said.

Hamyatta - adding the .FileType = 4 will certainly make your code run/work, but I have to add comments.

This is a bad interface with the user. All those message boxes are simply poor design. Figure out exactly what you want to do, do it, then inform the user if that is useful.

If it is NOT useful...and IMHO your messages are not, then don't give the messages.

Why do I think your messages are not useful?

1. You inform the user that they are going to search for "X" file types. Hmmmm, don't they know that? How are starting this procedure in the first place? Are you giving them a choice of files types to look for? If not...why tell them this? Further, BIG DEAL! It is not like you are giving them a Cancel option at this point. Your messagebox has no Cancel. They can't back out even if they want to at this point. So...what is the point to this message?

2. Next you display a message with the number of files found. How relevant is this? The user does NOT have any information about what the filenames are, so they get "File found: 7"; they get "File found: 18"; they get "Files found: 23". Is this really useful information? And again, the user has NO way to back out at this point either. Which leads to.....

3. The next messagebox displays the name of the just opened file. First of all, the user will be able to SEE the file is opened. The name of the file is at the top, just like any other oepned file. So the point of this messagebox is???? OK, now you have a Cancel. However....
if they DO Cancel...this file is already opened. So what exactly is the Cancel for? Yes, it stops any other files from being opened, BUT...

As you have given no information to the user regarding what the other (possible) files may be...how do they know to cancel or not? Maybe they do...maybe they don't.

I think you need to possibly rethink what exactly it is you want to happen.

A - are you trying to give a choice to the users on what file to open?

B - are you opening all the files regardless of the user.

Is seems you are doing a bit of both, which is not very good design. If you are trying to give a choice, then build an array of the filenames and let the user make the choice.

If you are trying to open them all, then...open them all.

Gerry
My paintings and sculpture

 

[PHV]

Skip simply said to get rid of the following line:
C:\ drive.

 

[fumei]

Oh. Thanks PH. Hmmm, yeesssssss, that certainly needed to go. I just took it out without thinking.

Gerry
My paintings and sculpture

 

[Hamyatta]

Hi Gerry,

Great to hear from you Gerry. Please find below answers tyo your questions... ( I will use large letters, sorry...to differentiate). Thanks again.

A - are you trying to give a choice to the users on what file to open?
NO I AM GIVING USERS THE CHOICE ON WHICH FILE TO OPEN..

B - are you opening all the files regardless of the user.
YES, I AM OPENING ALL FILES..

MY CODE IS IN "MAIN.XLS"; ALSO I AM DIRECTLY COPYING FROM EXCEL WORKBOOKS...

I NEED TO COPY (PREDEFINED) CELLS FROM EACH SOURCE INTO AN EXISTING WORKBOOK "MAIN.XLS" ON THE SAME WORKSHEET

THE TARGET SHEET ALREADY EXISTS IN "MAIN.XLS"
THE SHEET DOES NOT NEED TO BE RE-CREATED EVERY TIME.

I CLOSE ALL SOURCE FILES ONCE I'M DONE COPYING THOSE SPECIFIC CELLS...

SOME CELLS WILL BE BLANK IN FEW SOURCE FILES.. THAT'S OK AS I'll LATER FILTER THEM OUT...

Thanks in advance ...

 

[fumei]

Hamyatta,

1. I would suggest you look at the FAQ here on how to do posts. faq707-5689

Using CAPS FOR TEXT IS NOT POLITE. It is the equivalent to shouting. Why are you shouting? I see no need for using full caps. Even more oddly, you state you are deliberately using large letters. Please don't. A word here and there is OK, but having your reply in full caps is rude.

2. I frankly don't care what you are doing with the files. That was the not the subject of your post. If you are asking questions about what actions you are doing with the files - copying things from sheets, etc. etc. etc....then post a question on that. So telling me about target sheets, or if they need to be created or not....is not relevant to either your subject of the post, OR to the comments I made.

A - are you trying to give a choice to the users on what file to open?
NO I AM GIVING USERS THE CHOICE ON WHICH FILE TO OPEN..

B - are you opening all the files regardless of the user.
YES, I AM OPENING ALL FILES..

Please read that slowly.

My question: Are you trying to give a choice to the users?
Your answer: No I am giving the users the choice.

Hmmm. This is confusing. You say "No", but also say you are giving them a choice.

First of all, I explained that you are not, in fact, giving them a real choice. The files are opened sequentially. Say there are three files. The user wants to open #2. Your code will open #2, but it opens #1 first anyway. This is not a choice. With your code the user MUST open #1 to be able to open #2. Say there are 12 files, and the user wants to open #9. Well, with your code the user MUST open #1, #2, #3, #4, #5, #6, #7, AND #8 to get to open #9. This is not a choice.

Second, you state you want to give the user a choice, yet you also state you are opening all the files.

Perhaps you should look up "choice" in a dictionary.

I will repeat. Either you are giving a choice, OR, you are opening all the files.

Giving a choice means the user can choose to NOT open a file.

Opening all the files means...opening all the files. In which case, what choice does the user have? None.

Read what you wrote again.

A - are you trying to give a choice to the users on what file to open?
NO I AM GIVING USERS THE CHOICE ON WHICH FILE TO OPEN..

B - are you opening all the files regardless of the user.
YES, I AM OPENING ALL FILES..

Sorry, but you can not have it both ways. Either you are opening all the files - in which case the user has no choice; or you are giving the user a choice.

Again, perhaps you need to rethink this, and make a decision as to what exactly you are doing.

And I will reiterate - all the messages are distracting, and likely not needed. Messages to users should give them information they need.

Gerry
My paintings and sculpture

 

[Hamyatta]

Hi Gerry,

Sincere apologies for the impression... I was attempting to show the answers to your questions more clearly. I had no intention to shout at you... Again sorry.

You are also right that one of my answers was confusing..

-- In fact once the codes start to open the files, it opens all of them. Perhaps the code numbers them internally/sequentially in which case it would be a good thing. The user is not given a choice at all (to clarify further...)..

-- Then like you said, the codes will perform the copy/paste thing and close that source file before moving to the next source file.

ps: I am sorry again if you thought I was shouting.. my apologies.

Regards,
Hamyyatta

 

[SkipVought]

Jerry, you are patient. I gave up!

Skip,

_[red][/red]

 

[fumei]

I am not done yet......

Hamyatta, apologies accepted.

This is BAD design. And I again suggest you rethink what you are doing. Ask yourself -

WHAT do I want to the user to do? The user. Not you, as the coder. If YOU are processing actions on a number of files, then once the actions start, and the user has no direct input to those actions....then telling them stuff is pointless. Maybe tell them it is complete, when it is complete.

The point being, if the user is not actually doing anything, then don't pretend they are.

Next. Opening all the files at once is a serious waste of resources. If you have an action to perform on a series of files it is much, much, better to perform the actions on each file individually. You have to anyway. So open the first file, do your actions, close (and assumably save it!) that file, THEN go on to the next one.

So assuming you are correct - you want to action ALL the files, let's look at your code.

 ' ....other stuff...
  For lngCount = 1 To .FoundFiles.Count
      Workbooks.Open (.FoundFiles.Item(lngCount))
      If MsgBox(.FoundFiles.Item(lngCount), _
         vbOKCancel, "Found files") = vbCancel Then
         lngCount = .FoundFiles.Count
      End If
  Next lngCount

As I stated, this is not a good message, although yes, it does permit the cancelation of the process. But it is a BAD process. Let's get rid of the message.

  For lngCount = 1 To .FoundFiles.Count
      Workbooks.Open (.FoundFiles.Item(lngCount))
         ' do the code for EACH file here
         ' it will run on each file...then
         ' go to the next one
  Next lngCount

There. If what you want is to process action on all the files...then do exactly that.

Gerry
My paintings and sculpture

 

[JerryKlmns]

Skip

I am Jerry whilst fumei is Gerry . But it 's Ok

Gerry

You are what Skip says!

 

[SkipVought]

(J/G)errys,

So, I stand accused of (J/G)erry...mandering!

It's because of my (J/G)erry...atric state.

I cannot blame it on a (J/G)erry...nuk, bounding thru my space.


Skip,

_[red][/red]

 

[fumei]

Skip,

sounds....more like meandering......

Gerry
My paintings and sculpture