Shell not looking in the correct area

poorwallace

Active Member
Joined
Mar 8, 2005
Messages
365
I have some code that's supposed to save the currently open Excel file as a .txt file and then reopen it (the macro runs from a different Excel file). The problem is that the code looks in a completely different folder from my desktop (where the file is not located at). Is there a way to make the shell function look in a specific spot?

Thanks in advance; right now I'm not seeing why this isn't working (as it has worked in the past)....here is the code


Code:
   ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\wally\My Documents\BB_Upload.txt", FileFormat:= _
        xlText, CreateBackup:=False
    ActiveWorkbook.Save
    ActiveWindow.Close
   Application.DisplayAlerts = True
       
    Windows("Part2a.xls").Activate
    Sheets("Sheet1").Activate
    
    Windows(x).Activate
    Dim Q As String
    Q = Range("B2")
    
    Sheets.Add.Name = Q & " Returns Sheet"
    'Shell "notepad.exe C:\Documents and Settings\wally\My Documents\BB_Upload.txt"
   Shell "C:\Program Files\Windows NT\Accessories\WORDPAD.EXE BB_Upload.txt"

Application.ScreenUpdating = True
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

rjp

Board Regular
Joined
Jan 5, 2006
Messages
148
Try replacing:

Code:
Shell "C:\Program Files\Windows NT\Accessories\WORDPAD.EXE BB_Upload.txt"

with:

Code:
Shell "C:\Program Files\Windows NT\Accessories\WORDPAD.EXE ""C:\Documents and Settings\wally\My Documents\BB_Upload.txt"""

You just need to enclose the filename in " " as it contains spaces.
By using "" rather than " VBA knows that you want a speechmark and you're not terminating the string.
 

poorwallace

Active Member
Joined
Mar 8, 2005
Messages
365
Rob,

Thanks a bunch for the response. That worked great! I didn't know that about the Shell feature, so thanks for helping me learn something new.

Wally
 

Forum statistics

Threads
1,136,308
Messages
5,674,993
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top