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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
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
 

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Maybe try:

ActiveWorkbook.Close SaveChanges:=False
 

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
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
 

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259

ADVERTISEMENT

SORTED USING

application.displayalerts = false

&

application.displayalerts = true

THANKS ANDREW!!!!!!!!!!!!
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Sorry, I don't know why that should be. Do you get an error on SaveAs (or anywhere else)?
 

Forum statistics

Threads
1,136,517
Messages
5,676,319
Members
419,619
Latest member
jalme

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