Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-29-2019, 07:27 PM
Baiano42 Baiano42 is offline Macro to Save as Text to External Data Source Directory Windows 10 Macro to Save as Text to External Data Source Directory Office 2016
Novice
Macro to Save as Text to External Data Source Directory
 
Join Date: Jun 2019
Posts: 2
Baiano42 is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 06-29-2019, 08:11 PM
NoSparks NoSparks is offline Macro to Save as Text to External Data Source Directory Windows 7 64bit Macro to Save as Text to External Data Source Directory Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

My previous macro ???

How are you able to → "Get External Data: From Text" → without the path to it?
Reply With Quote
  #3  
Old 06-29-2019, 09:42 PM
Baiano42 Baiano42 is offline Macro to Save as Text to External Data Source Directory Windows 10 Macro to Save as Text to External Data Source Directory Office 2016
Novice
Macro to Save as Text to External Data Source Directory
 
Join Date: Jun 2019
Posts: 2
Baiano42 is on a distinguished road
Default

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
The problem with this code is that it is set to save to "ThisWorkbook.Path". Which, as the WB has not been saved anywhere, it does not have a path to reference to. (See attached images for further reference).

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.
Attached Images
File Type: jpg Imported Text Workflow.jpg (200.9 KB, 8 views)
File Type: jpg External Data Source Location.JPG (82.3 KB, 8 views)
Reply With Quote
Reply

Tags
external data, save as, vba code

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to Save as Text to External Data Source Directory How to always link source data (.xlsx) to same directory as Mailmergefile? beefcake2000 Mail Merge 6 01-24-2018 02:52 AM
Macro to Save as Text to External Data Source Directory Excel data linked to external source Doug Needham Excel Programming 1 09-15-2015 05:14 AM
Macro to Save as Text to External Data Source Directory drop down list from external data source Excel 2007? Intruder Excel 1 08-03-2012 05:41 AM
Macro to Save as Text to External Data Source Directory vba change external data source excelledsoftware Excel Programming 1 07-17-2012 04:22 AM
Macro to Save as Text to External Data Source Directory Refresh of data from external source Jirik61 Excel Programming 3 07-13-2012 01:40 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:15 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft