How to import specific data from Excel file to text file and import the data from generated text file into new Excel file by one macro

0
votes

I am new to VBA so not have good idea. I have written a code but I am not able to perform specific tasks as I want to perform. If somebody helps me out then I shall be grateful to you. Tasks:

I want to develop just one macro that automatically

1- Import Data from specific cells from Excel sheet to .txt file.

2- Import that data from created .txt file into new excel file. (Note Not want to get data in same excel file I want a new excel file to get open with data in it).

enter image description here

The code I have written for importing data from specific cells in excel files is producing text file where I am not being able to get separate columns (as shown in figure 2 separate columns). This code gives me data but just in 1 column but I want to get data in 2 separate columns.

2nd part of my code works to copy data from text file to excel sheet but in same excel sheet data is being copied means in same excel workbook but in different sheet in that workbook but I want that data should be copied in some other / new excel file/workbook that automatically being generated.

Important every time I update data in excel sheet so data in generated .txt file and new excel file should also be keep on updating.Please have a look at the image/ picture attached.

My Code

Private Sub CommandButton1_Click()

Dim rng As Range
Dim Sourceworksheet As Worksheet
Dim DestFile As String
Dim cel As Range

Application.DefaultFilePath = "C:\Libraries\Documents\"

DestFile = Application.DefaultFilePath & "\Test.txt"
Open DestFile For Output As #1

Set Sourceworksheet = ActiveWorkbook.ActiveSheet
Set rng = Range("A6:B40")

For Each cel In rng.Cells
    Write #1, cel.Address & "|" & cel.Value2
Next cel
Close #1

MsgBox "txt file exported"

Dim X As Double
Dim TXT As String

Open "C:\Users\Documents\Test.txt" For Input As #1

X = 0
Do While Not EOF(1)
    Line Input #1, TXT
    Cells(1, 1).Offset(X, 0) = TXT
    X = X + 1
Loop
Close #1

End Sub
1
vbaexcel
Sorry from all i forgot to add code before. Thanks Shai Rado for improving my query . Really Thankful. – Peter
Just a thought: Making a macro to transfer the specific data to a new excel file then make it save as .txt and .xlsx? – Cyril
@Cyril this also works if you have something written for that can you please share it with me because i donot know how to copy specific excel cells data into new excel workbook. – Peter
did you need the intermediary text file? Or can you just copy to the new Excel file? Intermediary text seems superflous... – Scott Holtzman
Sorry for late reply. Thanks a lot Scott Holtzman for your reply. I know that but unfortunately I need it for some other task and also it is being asked from my supervisor we have to use the text file data somewhere else. – Peter

1 Answers

0
votes

Quick questions (will make assumptions in code):

.1) Do you have one file that you work with and update (fixed name)?

.2) Does the file you are creating actually need to auto-update?

.3) Why do you need a separate spreadsheet? It might work to even have a new Sheet in the existing Workbook.

If you have a fixed file that you log information into, you can make a second excel file with the labels you want, then you can make the values link directly to the fixed file, e.g.:

A1 = "# of new TTNR:", then B1 = "FILEPATH/[FIXED NAME.xlsx]Sheet1!B2"

If you did this, you would get a prompt on loading the file containing that info to update the cell values, where Excel will pull the named data explicitly.

If you didn't have a fixed file name/location, you can make a macro to pull that value and name, dynamically.

I'd like to think that the above will help you, as a non-VBA solution. The assumptions made are that you have a fixed file name which your original data is in, and that your "truncated" file can also have a fixed name.