excel export to txt file without tabs

AnthonyMinnaar

New Member
Joined
Aug 25, 2020
Messages
17
Office Version
  1. 2013
Platform
  1. Windows
hi Folks,

I'm trying to make a post for a Fanuc milling machine. I've got most of it working now, except for one thing. The problem are the tabs behind the lines when exported to .TAP, the machine cant read these tabs. Is there an option to add to the code below so it leaves out these tabs? (I know, it's a huge mess, but it does the job. I can't write code myself, but I do know how to copy/paste, hence the chaotic code).

[Sub POSTB()
Sheets("PROGRAMMA").Select
Columns.EntireColumn.Hidden = False
Rows.EntireRow.Hidden = False
For Each c In Range("A1:A70")
If c.Value = "" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next
Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Worksheets("PROGRAMMA").Range("A1:G68")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
WorkRng.SpecialCells(xlCellTypeVisible).Copy
wb.Worksheets(1).Paste
saveFile = Application.GetSaveAsFilename(fileFilter:="TAP (*.TAP), *.TAP")
wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Sheets("INVULSHEET").Select
End Sub]
 

Attachments

  • Knipsel.PNG
    Knipsel.PNG
    7.6 KB · Views: 10

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,346
Office Version
  1. 365
Platform
  1. Windows
It might have more to do with the data than the code.
We would really need access to a data file to run your code against to see what is happening.

If you could upload one to a file sharing site, and provide a link to the file, someone may be able to take a look at it and see if they can tell what is going on.
Just be sure to replace any sensitive data with dummy data before uploading!
 

AnthonyMinnaar

New Member
Joined
Aug 25, 2020
Messages
17
Office Version
  1. 2013
Platform
  1. Windows
Hi Joe,

Here's a link to the file: Fanuc Post.xlsm

The first tab "invulsheet" has a button to 'post' the code that is in the last tab "programma".

I hope it is the point of the file is understandable and readable, as I'm quite new to Excel.

there's no sensitive data in the sheet, so it is the original file.

Most in the sheet is Dutch by the way, hope that won't cause any problems :)
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,146
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
according to your workbook as it is if you attach the expected result text file it could be easier to understand your need …​
 

AnthonyMinnaar

New Member
Joined
Aug 25, 2020
Messages
17
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Most if it is working as needed, so pressing the "post" button will already output the file and asks where it needs to be saved.

here's a link to a file that the sheet puts out once pressing the "post" button: program.TAP

the long blank spaces behind most text lines is what the machine can't read.
Hi,​
according to your workbook as it is if you attach the expected result text file it could be easier to understand your need …​
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,146
Office Version
  1. 2010
Platform
  1. Windows
According to the initial post and your attachments as a beginner starter :​
VBA Code:
Sub POSTB()
        V = Application.GetSaveAsFilename("program .TAP", ",*.TAP")
        If V = False Then Exit Sub
        F% = FreeFile
        Open V For Output As #F
    With Blad4
        For R& = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
            If .Cells(R, 1).Text > "" Then If .Cells(R, 2).Text = "" Then Print #F, .Cells(R, 1).Text Else _
                Print #F, Join(Application.Index(Range(.Cells(R, 1), .Cells(R, 1).End(xlToRight)).Value2, 1, 0), vbTab)
        Next
    End With
        Close #F
End Sub
 
Solution

AnthonyMinnaar

New Member
Joined
Aug 25, 2020
Messages
17
Office Version
  1. 2013
Platform
  1. Windows
According to the initial post and your attachments as a beginner starter :​
VBA Code:
Sub POSTB()
        V = Application.GetSaveAsFilename("program .TAP", ",*.TAP")
        If V = False Then Exit Sub
        F% = FreeFile
        Open V For Output As #F
    With Blad4
        For R& = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
            If .Cells(R, 1).Text > "" Then If .Cells(R, 2).Text = "" Then Print #F, .Cells(R, 1).Text Else _
                Print #F, Join(Application.Index(Range(.Cells(R, 1), .Cells(R, 1).End(xlToRight)).Value2, 1, 0), vbTab)
        Next
    End With
        Close #F
End Sub
You're a legend, Marc! this indeed puts out what I asked for! the code is so tidy. I've got a lot to learn! thanks a tonne :)
 

Forum statistics

Threads
1,136,805
Messages
5,677,829
Members
419,723
Latest member
jamieherethree

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top