How to export/program/macro from excel to .txt file?

Good evening from Norway,

I have a delicate issue that I need some help with. I have a spreadsheet with loads of data rows and columns that need to be exported to a .txt file for upload to a database. Hence, the spesific format is what I am struggeling with.

The format needs to be like this:

“Customerno” “Name” “Address” zip code” “zip code name” “Phone” ”MecCode” ”SalesCode”
"134871" "MOLO KYST RESTAURANT" "VALLEVEIEN 1000" "3960" "STATHELLE" "99627181" "" "233"
"129978" "BURGY SIRKUS SHOPPING" "FALKENBORGVEIEN 5" "7044" "TRONDHEIM" "97969552" "" "534"


I have no idea of how to do this, but maybe one of you guys can help?

Attached the spreadsheet with the data. If this is possible with a formula, it would be great to have it in column Q or R

Hi Pierre,

Thank you for your help. Unfortunately, I am not able to get the code to work.

Here is the macro I made:

Sub Main()
Dim C_ell As Range
Open "C:\temp\Logistikkhuset.txt" For Output As 1
Print #1, Chr(34) & "Custmerno" & Chr(34) & " " & _
         Chr(34) & "Name" & Chr(34) & " " & _
         Chr(34) & "Address" & Chr(34) & " " & _
         Chr(34) & "zip code" & Chr(34) & " " & _
         Chr(34) & "zip code name" & Chr(34) & " " & _
         Chr(34) & "Phone" & Chr(34) & " " & _
         Chr(34) & "SalesCode" & Chr(34)
For Each C_ell In Range("A2", Cells(Rows.Count, 1).End(xlUp))

   Print #1, Chr(34) & C_ell.Text & Chr(34) & " " & _
            Chr(34) & C_ell.Offset(0, 1).Text & Chr(34) & " " & _
            Chr(34) & C_ell.Offset(0, 2).Text & Chr(34) & " " & _
            Chr(34) & C_ell.Offset(0, 3).Text & Chr(34) & " " & _
            Chr(34) & C_ell.Offset(0, 4).Text & Chr(34) & " " & _
            Chr(34) & C_ell.Offset(0, 5).Text & Chr(34) & " " & _
            Chr(34) & C_ell.Offset(0, 10).Text & Chr(34)
Next
Close #1
End Sub

I removed the line related to "MecCode", as I have to make a separate macro in a different workbook for this line.

Where is it going wrong? I am not able to write the data to the .txt file, but I get no error message.

Hi,

So sorry to bother you, I discovered that the error was entirely mine. I created a .txt document on the designated output place, and the macro did not like to have a document already there with the desired name on it. It works like a charm!

Hi,

I know, it has been modified and luckily the macro provided above by p24leclerc works like a charm

Thank you for your help!

Hi,

I have a similar challenge that I cannot figure out. I am trying to do exactly the same, but with different information, creating a new output file. The headings are in cell H1 to J3, and the actual data is downwards from H3:J3 to H2500:J2500

The heading is as guessed, "MEC", "Customer" and "CCTR"

I am not able to get this macro to work, the error code is "Compile Error, Syntax error".

Here is the macro:

Sub MECToTXT()
Dim C_ell As Range
Open "C:\Users\B31090\Desktop\Logistikkhuset MEC.txt" For Output As 1
Print #1, Chr(50) & "MEC" & Chr(50) & " " & _
         Chr(50) & "Customer" & Chr(50) & " " & _
         Chr(50) & "CCTR" & Chr(50) & " " & _
For Each C_ell In Range("H3", Cells(Rows.Count, 1).End(xlUp))
   Print #1, Chr(50) & C_ell.Text & Chr(50) & " " & _
            Chr(50) & C_ell.Offset(0, 1).Text & Chr(50) & " " & _
            Chr(50) & C_ell.Offset(0, 2).Text & Chr(50) & " " & _


Next
Close #1
End Sub

I appreciate any help, my VB skills is rather poor I'm afraid...

Hi Leith Ross,

As far as I could figure out, it represents the length of the characters...? If it is wrong, please feel free to correct. As I mentioned before, this is my first steps into VB so I am a real rookie in this field.

Hi Leith Ross,

As far as I could figure out, it represents the length of the characters...? If it is wrong, please feel free to correct. As I mentioned before, this is my first steps into VB so I am a real rookie in this field.

Hi Leigh,

That is correct. Here is an example of the sorted data that I need to have in the format specified earlier. Note that the position of the rows are exactly as in my original workbook.

Example.xlsx

Hi Leith,

Thank you so much, the macro works like a charm!!

Hi again,

Maybee you also can help me with the following:

I would like to create a macro that takes the two output files that are stored on my desktop and ad them as attachments to an email. I do not want to send it, but it the macro could include the recepient as "xx@logistikkhuset.no"

Is this possible?

Hi,

Yes I am, I am using Outlook 2010 version.

Thank you so much!

And for a newbie like me, where do I mark this thread as solved?

Hi Leith Ross,

I have another question for you if you have the time.

I need to expand this macro to be valid for four columns in stead of three.

Here is the code:

Sub MECToTXT()
    Dim Data As Variant
    Dim Filespec As String
    Dim Rng As Range
    
        Filespec = "C:\Users\B31090\Desktop\Logistikkhuset MEC.txt"
        
        Set Rng = Range("H1:K1")
        Set Rng = Range(Rng, Cells(Rows.Count, Rng.Column).End(xlUp))
        
        Open Filespec For Output As #1
            For Each Row In Rng.Rows
                Data = Row.Value
                Data = Application.Transpose(Data)
                Data = Application.Transpose(Data)
                Data = Application.Transpose(Data)
              Print #1, """" & Data(1) & """ """ & Data(2) & """ """ & Data(3) & """ """ & Data(4) & """ """ & """"
            Next Row
        Close #1

I get an error saying that the subscript is out of range, and the problem should be here: Print #1, """" & Data(1) & """ """ & Data(2) & """ """ & Data(3) & """ """ & Data(4) & """ """ & """"

The last code was valid for

Set Rng = Range("H1:J1")
Set Rng = Range(Rng, Cells(Rows.Count, Rng.Column).End(xlUp))

Would you please let me know what I am doing wrong here?