VB in 2004 - how to import a text file from a network directory?

[root729]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi,

I have some code that I have always used on my local mac but now need to run on a second which is networked. The importing of the file was done by simply recording a macro so the file path is set and relative to the HD name. I am not sure if there is a way to map a drive like I used to in PC land on the mac which would solve this problem but I need someway to reference the file on my mac from both macs. If I record the same import text file macro from the second mac the file path is set to only the user name but on my mac I get the full path from the HD. Any ideas on how to go about this? I have found some code which enables me to choose a directory and store that as a string which would work but I cant find out how to insert the value in the string into the path in the code.

The code which has the path is this from the main mac:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Macintosh HD:Users:Tonyownloads:ep.txt", Destination:=Range("A1"))

If I record the same macro on the second mac the resulting code looks like this:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Tonyownloads:ep.txt", Destination:=Range("A1"))

But using the second code on the first mac does not work.

Using the choose folder option I end up with a string but cant work out how to replace the path with a variable name so maybe someone could solve that one for me??

Any help greatly received, so far the only option I can see is to have 2 different files which would work but I am sure there is no need.

Many thanks,
Tony.

 

[Bob Greenblatt]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi,

I have some code that I have always used on my local mac but now need to run
on a second which is networked. The importing of the file was done by simply
recording a macro so the file path is set and relative to the HD name. I am
not sure if there is a way to map a drive like I used to in PC land on the mac
which would solve this problem but I need someway to reference the file on my
mac from both macs. If I record the same import text file macro from the
second mac the file path is set to only the user name but on my mac I get the
full path from the HD. Any ideas on how to go about this? I have found some
code which enables me to choose a directory and store that as a string which
would work but I cant find out how to insert the value in the string into the
path in the code.

The code which has the path is this from the main mac:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Macintosh HD:Users:Tonyownloads:ep.txt",
Destination:=Range("A1"))

If I record the same macro on the second mac the resulting code looks like
this:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Tonyownloads:ep.txt", Destination:=Range("A1"))

But using the second code on the first mac does not work.

Using the choose folder option I end up with a string but cant work out how to
replace the path with a variable name so maybe someone could solve that one
for me??

Any help greatly received, so far the only option I can see is to have 2
different files which would work but I am sure there is no need.

Many thanks,
Tony.

You could do several things. If you want to get the file path as a string,
use application.getopenfilename. Be careful, however, as this will NOT
return the correct and full path, if the user navigates to the file by
flipping the disclosure triangles in the dialog. If, instead, they double
click on folders to open them, then the path is correct. Some sample code
would look like:
Xxx=application.getopenfilename
...."Text;"&xxx,destination.....
Remember to chack for a null string, or False, if the user cancels out of
the dialog.

On the second mac, you have your main user mounted as a volume. Another way,
which should work on either mac, using code similar to the above would be:
If application.username="aaaa" then
xxx="Macintosh HD:Users:tony:downloads:ep.txt"
Elseif application.username="bbbb" then
xxx="tony:downloads:ep.txt"
Else
msgbox "I don't know where the file is!"
exit sub
End if

 

[root729]

hey, now that looks like the code for me, did not know about application.username so that looks like it will sort the problem of the 2 files ! great tip. the only problem I can see without getting into the code and trying is how do I inser the string into the query? Sorry if its a dumb question but lets say in your example I need to replace the file path with the contents of thevariable xxx . . .what do I need to get it to work . . .

In other words I need to replace everything from "TEXT; up to ep.txt with the content of xxx

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Macintosh HD:Users:Tonyownloads:ep.txt", Destination:=Range("A1"))

Would I simply close the " and add xxx or do I need something in there to tell the code to join it all together, like the &???

sorry again, I am not able to test right now but will give it a try later.

Thanks again for the help.

Tony.

 

[Bob Greenblatt]

hey, now that looks like the code for me, did not know about
application.username so that looks like it will sort the problem of the 2
files ! great tip. the only problem I can see without getting into the code
and trying is how do I inser the string into the query? Sorry if its a dumb
question but lets say in your example I need to replace the file path with the
contents of thevariable xxx . . .what do I need to get it to work . . .

In other words I need to replace everything from "TEXT; up to ep.txt with the
content of xxx

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Macintosh HD:Users:Tonyownloads:ep.txt",
Destination:=Range("A1"))

Would I simply close the " and add xxx or do I need something in there to tell
the code to join it all together, like the &???

sorry again, I am not able to test right now but will give it a try later.

Thanks again for the help.

Tony.

I answered this in my prior response. The syntax is:
"text;"&xxx,destination........