Opening text export file using call shell command

[Chris Freeman]

Hello,
Again, I'm a kind of advanced using Access in general, but a newbie to VBA
coding. I want to export a file to text, then open the file exported using
call shell to notepad. I can export the file fine, and I can open
Notepad.exe, but the file name inserted in the open command seems to cause
failure. This is what I have coded so far:

Dim Filename As String
Dim DataDir As String
Dim NP As String
On Error GoTo mdoExport_CSV_Err

'need to determine if a preferred file save path can be entered here
DataDir = CurDir
Filename = Forms!frm_Export_To_TXT.Combo0
DoCmd.TransferText acExportDelim, "", Forms!frm_Export_To_TXT.Combo0,
Forms!frm_Export_To_TXT.Combo0 & ".txt", False, ""
DoCmd.Close acForm, "frm_Export_To_TXT"
Filename = DataDir & "\" & Filename & ".txt"

'open notepad. determine method to open file
NP = "C:\WINDOWS\system32\notepad.exe"
Call Shell(NP & Filename, vbNormalFocus)

So far it all works individually, and all parameters are correct. Pathname
for the FileName opens the file properly on its own, but using NP and
FileName together causes error.

What's the correct Shell command string I should use?

Thanks in advance

 

[Douglas J. Steele]

You're missing the space between the path to Notepad.exe and the path to the
text file:

Call Shell(NP & " " & Filename, vbNormalFocus)

However, since it's possible that your filename could have spaces in it,
you'll need quotes around the filename:

Call Shell(NP & " """ & Filename & """", vbNormalFocus)

That's double quote, space, three double quotes in a row before Filename,
and four double quotes in a row afterwards.

That being said, you could just as easily use

Application.FollowHyperlink Filename

 

[Chris Freeman]

Doug, thank you. Both options worked perfect, except the hyperlink code
throws up the message about hyperlinks being harmful to your computer.

any way to by pass that warning message without having to change user
settings?

Thanks
--
Chris Freeman
IT Project Coordinator

You're missing the space between the path to Notepad.exe and the path to the
text file:

Call Shell(NP & " " & Filename, vbNormalFocus)

However, since it's possible that your filename could have spaces in it,
you'll need quotes around the filename:

Call Shell(NP & " """ & Filename & """", vbNormalFocus)

That's double quote, space, three double quotes in a row before Filename,
and four double quotes in a row afterwards.

That being said, you could just as easily use

Application.FollowHyperlink Filename

 

[Douglas J. Steele]

I believe you can set Macro Security to Low, and you won't be prompted.

It's also possible that the API approach illustrated in
http://www.mvps.org/access/api/api0018.htm at "The Access Web" will avoid
the problem.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug, thank you. Both options worked perfect, except the hyperlink code
throws up the message about hyperlinks being harmful to your computer.

any way to by pass that warning message without having to change user
settings?

Thanks

 

[Chris Freeman]

Doug,
One more follow up. When I run the Call Shell code for the Winzip app, it
opens the app. But I've noticed that after running the extract, the shell
stays visible. What's the opposite of Call Shell?
--
Chris Freeman
IT Project Coordinator

You're missing the space between the path to Notepad.exe and the path to the
text file:

Call Shell(NP & " " & Filename, vbNormalFocus)

However, since it's possible that your filename could have spaces in it,
you'll need quotes around the filename:

Call Shell(NP & " """ & Filename & """", vbNormalFocus)

That's double quote, space, three double quotes in a row before Filename,
and four double quotes in a row afterwards.

That being said, you could just as easily use

Application.FollowHyperlink Filename

 

[Douglas J. Steele]

There isn't an opposite that I'm aware of.

You could try shelling to a BAT file that calls Winzip and then exits. There
are alternatives to Winzip as well.