#1
|
|||
|
|||
Macro to Save as Text to External Data Source Directory
Dear Excel Experts,
I am trying to make a macro (in my personal macro workbook) that can export my adjusted data from an external source to the same folder as the external source. My workflow as follows: Generate a text file from another software → Open a new excel workbook → "Get External Data: From Text" → Adjust the data → Save as Text Tab Delimited to external source folder My previous macro to save as text doesn't work in this case, as it is directing the save to the directory of the active workbook and the workbook I'm using hasn't been saved anywhere. My goal is to be able to 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 workbook itself. |
#2
|
|||
|
|||
My previous macro ???
How are you able to → "Get External Data: From Text" → without the path to it? |
#3
|
|||
|
|||
So this was the macro I had been using to save as text before:
Code:
Option Explicit Sub SaveSheetToTxt() 'UpdatebyBaiano42 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 So I do have the 'path' in a sense. However, as I am constantly building new projects, I'm trying to make the macro be able to direct the new .txt document save to the current external source document I have sourced to the WB. |
Tags |
external data, save as, vba code |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to always link source data (.xlsx) to same directory as Mailmergefile? | beefcake2000 | Mail Merge | 6 | 01-24-2018 02:52 AM |
Excel data linked to external source | Doug Needham | Excel Programming | 1 | 09-15-2015 05:14 AM |
drop down list from external data source Excel 2007? | Intruder | Excel | 1 | 08-03-2012 05:41 AM |
vba change external data source | excelledsoftware | Excel Programming | 1 | 07-17-2012 04:22 AM |
Refresh of data from external source | Jirik61 | Excel Programming | 3 | 07-13-2012 01:40 PM |