PDA

View Full Version : [SOLVED] Saving Current Worksheet as Text in Current Directory



Baiano42
06-28-2019, 01:15 PM
I currently have a macro that lets me save my sheet using the current sheet's data and tab name as text, which I hope to keep. However, when I try to save it, it automatically takes me to the 'My Documents' folder. Could someone please help me make the code automatically direct the new save as to the current directory of the Excel file? Thanks!

Sub SaveSheetToTxt()
'Updateby20150910
Dim xRet As Long
Dim xFileName As Variant
On Error GoTo ErrHandler:
xFileName = Application.GetSaveAsFilename(ActiveSheet.Name, "Text (Tab Delimited) (*.txt), *.txt", , "Kutools for Excel")
If xFileName = False Then Exit Sub
If Dir(xFileName) <> "" Then
xRet = MsgBox("File '" & xFileName & "' exists. Overwrite?", vbYesNo + vbExclamation, "Kutools for Excel")
If xRet <> vbYes Then
Exit Sub
Else
Kill xFileName
End If
End If
ActiveSheet.Copy
ActiveWorkbook.SaveAs xFileName, xlUnicodeText
If ActiveWorkbook.Name <> ThisWorkbook.Name Then
ActiveWorkbook.Close False
End If
My_Exit:
Exit Sub
ErrHandler:
MsgBox Err.Description, , "Kutools for Excel"
End Sub

Cheers,

Baiano42

Paul_Hossler
06-28-2019, 02:16 PM
If I run this on my Desktop, it opens the GetSaveAsFilename dialog there. Is that what you were looking to do?



Option Explicit

Sub SaveSheetToTxt()
'Updateby20150910

Dim xRet As Long
Dim xFileName As Variant
Dim wbText As Workbook

On Error GoTo ErrHandler:

ChDir ThisWorkbook.Path '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

xFileName = Application.GetSaveAsFilename(ActiveSheet.Name, "Text (Tab Delimited) (*.txt), *.txt", , "Kutools for Excel")

If xFileName = False Then Exit Sub

If Dir(xFileName) <> "" Then
xRet = MsgBox("File '" & xFileName & "' exists. Overwrite?", vbYesNo + vbExclamation, "Kutools for Excel")

If xRet <> vbYes Then
Exit Sub
Else
Application.DisplayAlerts = False
Kill xFileName
Application.DisplayAlerts = True
End If
End If
ActiveSheet.Copy
Set wbText = ActiveWorkbook

wbText.SaveAs xFileName, xlUnicodeText
wbText.Close False
My_Exit:
Exit Sub
ErrHandler:
MsgBox Err.Description, , "Kutools for Excel"
End Sub

Baiano42
06-28-2019, 03:33 PM
Paul, thank you for your reply,
Unfortunately, that took me to
...\AppData\Roaming\Microsoft\Excel\XLSTART

I've been tinkering with another set of code, and it too seems to be saving it to the same area:


Sub SaveAsTxt()
'
' UpdateByBaiano42
'
ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name, FileFormat:=xlText, CreateBackup:=False
End Sub

It is shorter, saves the data like the previous set of code provided, and it too saves to the ...\app data\roming\... and not the current directory like I'm wanting... And this set of code does the same too:


Sub SaveAsTxt()
' SaveAsText Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & ActiveSheet.Name & ".txt", _
FileFormat:=xlText, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=True
End Sub


Still gets the save as text, to the name of the sheet, but still not directed to the current directory.

Paul_Hossler
06-28-2019, 03:57 PM
What folder is the macro containing Excel file in?



Could someone please help me make the code automatically direct the new save as to the current directory of the Excel file? Thanks!




What do you mean by "Current Directory"?

If my XLSM is in Desktop, then the dialog uses



ChDir ThisWorkbook.Path '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


to start in Desktop

24528

Baiano42
06-28-2019, 04:27 PM
I have my macros saved in my Macro Book, because I need them to be used in multiple work books. The current XL Work book I'm working with is saved in '\Documents\Current Jobs\6-28-19 Sally\Plans'. I often am changing folder directories, and need the macro to keep up with it. Tomorrow the folder may be ...\6-29-9 Sally\...
'Current Directory' means the folder where the XL spreadsheet is located that contains the current data I'm trying to save as text.

Paul_Hossler
06-28-2019, 05:13 PM
You didn't say that the macro was in your Personal.xlsm

Try changing the line to this then and see if it works


ChDir ActiveWorkbook.Path '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


If you are running a macro in your Personal.xlsm, ThisWorkbook.Path would be XLSTART

Baiano42
06-28-2019, 11:04 PM
Unfortunately, the

ChDir ActiveWorkbook.Path '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Still tries to save it to the XLSTART folder. Sadly, I need the macro to be saved there, cause I need to use it on different documents as I use multiple workbooks that other people generate and don't have any macros saved to it. Any other tricks that I could try?

Baiano42
06-28-2019, 11:35 PM
Is there a way to define the current directory to where the workbook containing the data is located, and not where the the macro is located, and then have it save as text?

Baiano42
06-29-2019, 06:49 AM
NVM, I got it to work with the ChDir ActiveWorkbook.Path '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Thanks Paul, you are amazing!
One more question, I also need to open text files occasionally, and need to do some adjustments to those files. When trying to save them back as a text, the code won't quite work, as the Workbook hasn't been saved anywhere. Current work flow is:
Open excel->Open from text->(Do edits)->Save As->Save in folder
However, using that method, the ActiveWorkbook hasn't been saved, so there is no target to save to.

Question: how can I set the save target to the source text file?

Paul_Hossler
06-29-2019, 06:57 AM
The Activeworkbook.Path is the path to the WB that has the sheet in it, not the macro containing one in XLSTART (unless that's where you put the sheet WB)

It works for me

I made a hidden WB with the macro and saved it to XLSTART

Opened another XLXS on the Desktop with a WS and ran the macro and it saved the TXT file to the Desktop


Opened another XLXS inDocuments with a WS and ran the macro and it saved the TXT file to Documents

BTW I don't know why you're using GetSaveAsFileName. No needed since you have the Path from ActiveWorkbook and the file name from the WS name

Look at SaveAsText2 below

24533





Option Explicit
Sub SaveAsText1()

Dim xRet As Long
Dim xFileName As Variant
Dim wbText As Workbook
On Error GoTo ErrHandler
ChDir ActiveWorkbook.Path
xFileName = Application.GetSaveAsFilename(ActiveSheet.Name, "Text (Tab Delimited) (*.txt), *.txt", , "Kutools for Excel")
MsgBox xFileName

If xFileName = False Then Exit Sub

If Dir(xFileName) <> "" Then
xRet = MsgBox("File '" & xFileName & "' exists. Overwrite?", vbYesNo + vbExclamation, "Kutools for Excel")

If xRet <> vbYes Then
Exit Sub
Else
Application.DisplayAlerts = False
Kill xFileName
Application.DisplayAlerts = True
End If
End If
ActiveSheet.Copy
Set wbText = ActiveWorkbook

wbText.SaveAs xFileName, xlUnicodeText
wbText.Close False
My_Exit:
Exit Sub
ErrHandler:
MsgBox Err.Description, , "Kutools for Excel"
End Sub




Sub SaveAsText2()

Dim xRet As Long
Dim xFileName As Variant
Dim wbText As Workbook
xFileName = ActiveWorkbook.Path & Application.PathSeparator & ActiveSheet.Name & ".txt"
MsgBox xFileName ' remove later <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
If Dir(xFileName) <> "" Then
xRet = MsgBox("File '" & xFileName & "' exists. Overwrite?", vbYesNo + vbExclamation, "Kutools for Excel")

If xRet <> vbYes Then
Exit Sub
Else
Application.DisplayAlerts = False
Kill xFileName
Application.DisplayAlerts = True
End If
End If

ActiveSheet.Copy
Set wbText = ActiveWorkbook

wbText.SaveAs xFileName, xlUnicodeText
wbText.Close False

End Sub





If that isn't it, then please add a lot more details

Baiano42
06-29-2019, 09:29 AM
So here is the current workflow that I'm using (See below):
24536
Using the SaveAsText2, I receive the following prompt:
24537
And clicking Debug when prompted gives this:
24538

Paul_Hossler
06-29-2019, 12:40 PM
If the workbook with the data is open and has been previously saved, then could you just do it like this?

This uses the Path of the opened WB, not the one containing the macro, and the sheet name



Option Explicit


Sub SaveAsText4()
Dim sPath As String, sFilename As String, sOutputName As String

sPath = ActiveWorkbook.Path
sFilename = ActiveSheet.Name
sOutputName = sPath & Application.PathSeparator & sFilename & ".txt"

MsgBox sOutputName ' testing

If Dir(sOutputName) <> "" Then
If MsgBox("File '" & sOutputName & "' exists. Overwrite?", vbYesNo + vbExclamation, "Kutools for Excel") <> vbYes Then Exit Sub

Application.DisplayAlerts = False
Kill sOutputName
Application.DisplayAlerts = True
End If

ActiveWorkbook.SaveAs sOutputName, xlUnicodeText
ActiveWorkbook.Close False
End Sub

Baiano42
06-29-2019, 07:40 PM
That would work, but in this case, I'm hoping to either save a new .txt file or overwrite the old one to the external source folder (which changes per project I'm working on), without needing to save the excel WB. Having to save the WB in this case would entail manually finding and saving it to the external source folder, which is the part that I'm hoping to automate now.

Paul_Hossler
06-30-2019, 07:32 AM
So you want to ...


1. Have the macro in XLSTART

2. Select a TXT file (input.txt)

3. Load TXT into new WB (Book1)

4. Are you going to do something with the file once it's loaded??

5. Save new TXT file to same folder as input.txt with name = the sheet name

6. Close w/o saving Book1

?????





I currently have a macro that lets me save my sheet using the current sheet's data and tab name as text, which I hope to keep. However, when I try to save it, it automatically takes me to the 'My Documents' folder. Could someone please help me make the code automatically direct the new save as to the current directory of the Excel file? Thanks!

I guess I'm hung up / confused by the above. If you haven't saved the Excel file, then it's not in a directory


In my #12, no workbook needs to be saved, just the input needs to be open. That part can be automated to allow user select using GetOpenFileName().

Baiano42
06-30-2019, 08:30 AM
That is correct, I hope to do the following steps:

1. Have the macro in XLSTART

2. Select a TXT file (input.txt)

3. Load TXT into new WB (Book1)

4. Run a macro to process my data (I've got it to run a couple equations and filter out the data I don't need successfully)

5. Save new TXT file to same folder as input.txt with name = the sheet name

6. Close w/o saving Book1

(Ideally, I'd like to combine the macros for step 4 - 6, so that it would do the processing and then save it with one click)

Pardon me, but I'm not sure I follow. When you noted that the input needs to be open, and it can be automated, what do you mean by that? (I am very new to making macros, and am very grateful for your patience and assistance Paul).

Paul_Hossler
06-30-2019, 09:36 AM
Try this -




Option Explicit
Sub SaveAsText6()
Dim sPath As String, sFilename As String, sOutputName As String, sInputName As String
Dim i As Long

'get text file name from user, exit if canceled
sInputName = Application.GetOpenFilename("Text (Tab Delimited) (*.txt), *.txt", , "Kutools for Excel")
If sInputName = "False" Then Exit Sub

MsgBox sInputName ' testing

'open text file
Workbooks.Open Filename:=sInputName


'do some processing
ActiveSheet.Name = "Output"
ActiveSheet.Cells(1, 1).CurrentRegion.Value = 3456


'get path
i = InStrRev(sInputName, "\")
sPath = Left(sInputName, i)
sFilename = ActiveSheet.Name
sOutputName = sPath & sFilename & ".txt"
MsgBox sOutputName ' testing

If Dir(sOutputName) <> "" Then
If MsgBox("File '" & sOutputName & "' exists. Overwrite?", vbYesNo + vbExclamation, "Kutools for Excel") <> vbYes Then Exit Sub

Application.DisplayAlerts = False
Kill sOutputName
Application.DisplayAlerts = True
End If

ActiveWorkbook.SaveAs sOutputName, xlUnicodeText
ActiveWorkbook.Close False
End Sub

NoSparks
06-30-2019, 10:15 AM
@ Baiano42
seeing your picture in post #11 and at MSOfficeForums (https://www.msofficeforums.com/142900-post3.html), is it reasonable to think steps 2 & 3 of post #15 should be
2. open a new blank workbook (Book1)
3. load text into Book1 using the 'From Text' icon on the Data Ribbon

Baiano42
06-30-2019, 10:47 AM
@ Paul_Hossler
It's so the latest code set you provided gives me an "Open" window and is directed towards the Desktop when run. (See first image)

@ NoSparks
You are correct, stating it as follows would be more accurate:
1. Have the macro in XLSTART

2. open a new blank workbook (Book1)

3. load text into Book1 using the 'From Text' icon on the Data Ribbon

4. process data

5. Save new TXT file to same folder as input.txt with name = the sheet name (This would need to be a relative link, as I may be using different input.txt files in different sheets.)

6. Close w/o saving Book1

Ideally, I'd like to make it:
1. Have the macro in XLSTART

2. open a new blank workbook (Book1)

3. load text into Book1 using the 'From Text' icon on the Data Ribbon

4. Use macro to process data, Save new TXT file to same folder as input.txt with name = the sheet name, Close w/o saving Book1

*Aside: Looking at the data connection, I see that the source file directory is linked in the 'Definition: Connection file' and 'Edit Query' (See second image). Is there any way to make a relative link through either of those to get the save file to be directed to the desired location?*
2454024541

Paul_Hossler
06-30-2019, 10:52 AM
@ Paul_Hossler It's so the latest code set you provided gives me an "Open" window and is directed towards the Desktop when run. (See first image)

Yes, but you have to change folders to where ever the text file is, open it, etc.

Saving the result file will be in that folder (i.e. the same as input) with the sheet name used as the file name


There's no need for a blank workbook since the macro opens the text file and creates it's own workbook


I thought that's what you wanted

Baiano42
06-30-2019, 11:07 PM
Yes, but you have to change folders to where ever the text file is, open it, etc.

Not quite, so when I click to open the external source file, it creates an "Output.txt" file with a whole bunch of "3456" filling it.

Tinkering around with developer mode, I found this:


With ActiveWorkbook.Connections("Surface_A")
.Name = "Surface_A"
.Description = ""
End With
Range("A1:K41").Select
With Selection.QueryTable
.Connection = _
"TEXT;C:\Users\Bill Gates\Documents\Current Jobs\6-28-19 Sally\Plans\Surface_A.txt"


Is there any way to be able to use parts of the above code as a way to achieve step 5, while still maintaining a relative folder reference (as I'll be using different connections for different projects)?

Paul_Hossler
07-01-2019, 05:51 AM
1. The 3456 was just a placeholder for some processing. You said there were some things you wanted to do on the file before writing it out




'do some processing
ActiveSheet.Name = "Output"
ActiveSheet.Cells(1, 1).CurrentRegion.Value = 3456



2. You'd still have to browse to the Surface_A.txt file's folder in order to find where the Connection is and thus the desired output folder

3. Since the txt file is on disk, the Data Connection route seems like an unnecessary complication, compared to just opening the file directly, processing, and then saving an output file