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

[Grizlore]

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

 

[Grizlore]

No, no errors.... BUT.... I have fixed it, I think

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

So this concatenation seems to have fixed it.... ActiveWorkbook.SaveAs Filename:= _ CurrentLoc + "\" + "TareWeightText.txt

Thanks for ALL you help Andrew!