find the files loction and then saving a copy next to it

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
Hi,
I have a spreadsheet, which will be saved somewhere on our company's external drive on another site. I do not know where this will be and this could change.
This spreadsheet was planned to be copied automatically into one of my drives and then imported into another spreadsheet. However as this files has all kinds of Macros, I think it would be easier to, at the time of savng, have the initial master spreadsheet save a text file version of the required data next to it in the same folder.

So my question to you guys.....

How do i find where a file is saved (eg \\server1\public\Department\Its_here\) as i would need that to enable this text file to be saved in the same folder, wherever this file goes.

Any help would be appreciated
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
If a workbook is open:

ThisWorkbook.Path

will tell you where it's saved.

Many thanks.
I have watched your work from afar and always been impressed how you embrace Occam's Razor
 
Upvote 0
If a workbook is open:

ThisWorkbook.Path

will tell you where it's saved.


Many Thanks.

How can i save a copy of a spreadsheet as a text file, then close it once done, without it keep prompting the User to "sure you wanna over write?" or re-ask the 'save' question when it closes.

My text is as follows





Code:
    CurrentLoc = ThisWorkbook.Path
    
    Sheets("Data").Select
    Sheets("Data").Copy
    
    ChDir CurrentLoc
    
    ActiveWorkbook.SaveAs Filename:= _
        "TareWeightText.txt", FileFormat:= _
        xlText, CreateBackup:=False
    Windows("TareWeightText.txt").Activate
    ActiveWindow.Close 'it always re-asks the save questions etc
     
    Windows("Tare Weight Records.xls").Activate
    Range("A2").Select

HELP APPRECIATED
 
Upvote 0
Maybe try:

ActiveWorkbook.Close SaveChanges:=False

Code:
    CurrentLoc = ThisWorkbook.Path
    
    Sheets("Data").Select
    Sheets("Data").Copy
    
    ChDir CurrentLoc
    
    ActiveWorkbook.SaveAs Filename:= _
        "TareWeightText.txt", FileFormat:= _
        xlText, CreateBackup:=False
    Windows("TareWeightText.txt").Activate
    ActiveWorkbook.Close SaveChanges:=False
     
    Windows("Tare Weight Records.xls").Activate
    Range("A2").Select

Thanks, this does stop the second save prompt, however once this is used, and the TEXT file is saved, when you run it again it still prompts you asking whether you want to overwrite the exisiting file. Any thoughts on that?

Many Thanks
 
Upvote 0
SORTED USING

application.displayalerts = false

&

application.displayalerts = true

THANKS ANDREW!!!!!!!!!!!!
 
Upvote 0
If you don't want any prompts from Excel you can use:

Application.DisplayAlerts = False

at the beginning of your code. Set it back to True at the end.
 
Upvote 0
although....this code works great, with Andrews help...IF it sits on my desktop, once i move it to a network drive or anywhere else, it just doesnt save the text file..... any ideas???

PLEASE??? I am lost!!!!!

Code:
  Application.DisplayAlerts = False

    CurrentLoc = ThisWorkbook.Path
    
    Sheets("Data").Select
    Sheets("Data").Copy
    
    ChDir CurrentLoc
    'MsgBox CurrentLoc This is so i know for sure the location is correct
    
    ActiveWorkbook.SaveAs Filename:= _
        "TareWeightText.txt", FileFormat:= _
        xlText, CreateBackup:=False
    Windows("TareWeightText.txt").Activate
    ActiveWorkbook.Close SaveChanges:=False
     
    Windows("Tare Weight Records.xls").Activate
    Range("A2").Select
    
    Application.DisplayAlerts = True
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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
Back
Top