Importing Data from Multiple Excel Documents Into a Single File MAC Issues

[Kal_2013]

Dear All

Struggling to import data from multiple excel documents into a single file.

I found some helpful threads and codes but am struggling to implement them. I am pretty new to visual basic or you can say a novice.

I found these useful codes below from this thread: . I decided to start a new thread instead of jumping on the ones I read as I am told this is good practice. The first one hangs on the fourth line:

CurrentFile = Dir(strFldrPath & ".xls")

I think there might be some compatibility issues with directory structures etc. as I am using Excel 2011 for mac. I've used various file extensions xls, xlsx, xlsm but don't seem to make any difference. Can anyone suggest a quick fix to get this working?!

Many thanks,


Here's the code:

Please Login or Register  to view this content.

Moderator's Edit: Use code tags when posting code. To do so in future, select your code and click on the # icon at the top of your post window.

[OllieB]

I have no access to a MAC nor have I ever worked on one, but I am not really sure about the the statement below

Please Login or Register  to view this content.

Is this truly the path where your Excel files are stored? I am asking this because in the examples you referred to I think the ..... were intended to be replaced by the actual values. If the path is not correct the Dir instruction will not return any file name and the whole process will not function as desired.

Does this make sense?

[Kal_2013]

Thanks for the reply Ollie. Sorry I wasn't clear:

The '...' you refer to was the rest of the path to the folder. I didn't want to put in the rest of the path as I have to keep some of the folder titles private, but there are four other 'folder':'folder' etc ending with :"

If that makes sense?!

Any other ideas?

[OllieB]

Ok, lets assume the path is correct, then I would expect:

Please Login or Register  to view this content.

I added the "*", but as I have never worked on a MAC I am just guessing here. How do you actually know that "it hangs" on the fourth line?

Does strFldrPath end with a ":"?

[OllieB]

the Dir command as you are using it is not supported on a MAC. See the link below

http://stackoverflow.com/questions/1...excel-2011-vba

and

http://office.microsoft.com/en-us/ac...001228879.aspx

[Kal_2013]

Just tried the "*" and same problem. Excel 2011 has a debugging feature which identifies the problem. It gives the error message "Run-time error '68' Device not available" then takes you to that line of code.

Searching for the error codes hasn't helped in fixing the problem though it does seem like it's the directory structure it's struggling with.

[OllieB]

Just tried the "*" and same problem. Excel 2011 has a debugging feature which identifies the problem. It gives the error message "Run-time error '68' Device not available" then takes you to that line of code.

Searching for the error codes hasn't helped in fixing the problem though it does seem like it's the directory structure it's struggling with.

Did you read the links I have posted? You have to change the DIR statement for the MAC by using a MAC ID

[Kal_2013]

Hi OllieB

I sent the previous post before I'd received your last one. I've looked through the links you kindly posted. Thanks. I've substituted things as follows but still not working. Now getting error message " Run time error '13' Type mismatch"

Apologies if I'm doing something silly, but if anyone can stop the error please let me know...

Please Login or Register  to view this content.

[Kal_2013]

Dear All

Still struggling on this!

Decided to try things out on a windows machine running excel 2007.

Now seems to 'hang' on:

Set SrcRng = .Sheets("Sheet1").Range("A2,A5:05,A8:I8,A11:K11,A15:P15,A18:F18")

Struggling to make head or tail of this as I'm a complete novice to visual basic.

Any help much appreciated!

Please Login or Register  to view this content.