TransferText - using environment variable

[harry.liebman]

I'm trying to import from an HTML file, but I need to leverage
environment variables to find it.

I'm using a macro and specifying an action of TransferText. I've tried
putting in a filename of:

1) %userprofile%\extr.htm, which should expand to c:\documents and
settings\<myname>\extr.htm. It fails.
2) c:\documents and settings\%username%\extr.htm (should expand to same
value). It fails.
3) c:\docume~1\%username%\extr.htm. Fails also.
I've done each of these enclosed in quotes, or with just the
environment variable enclosed in quotes.

Is there a way to specify the filename so it'll work? I'm using Access
2002.

TIA,

Harry

 

[David Lloyd]

Harry:

I do not believe the TransferText method actually resolves the FileName
parameter. One option, if you want to use code, is to use the Environ
function to resolve the UserProfile environment variable and then pass this
string to the TransferText method. You could run the function from a macro
using the RunCode action.

For example:

Function ImportHTML()
Dim sFileName As String

sFileName = Environ("userprofile") & "\extr.htm"

DoCmd.TransferText acImportHTML, "MySpecification" , "MyTableName" ,
sFileName, True , MyHTMLTable

End Function

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I'm trying to import from an HTML file, but I need to leverage
environment variables to find it.

I'm using a macro and specifying an action of TransferText. I've tried
putting in a filename of:

1) %userprofile%\extr.htm, which should expand to c:\documents and
settings\<myname>\extr.htm. It fails.
2) c:\documents and settings\%username%\extr.htm (should expand to same
value). It fails.
3) c:\docume~1\%username%\extr.htm. Fails also.
I've done each of these enclosed in quotes, or with just the
environment variable enclosed in quotes.

Is there a way to specify the filename so it'll work? I'm using Access
2002.

TIA,

Harry