Hi,
I'm new to this Forum as to VBA and Macro's. I'mm hoping you can help me with the following since I'm not finding any copy/paste solution. Currently I'm working on an excel file that serves as input for InDesign. My challenge is to automate the following:
Excel formulas help me to fill a table that with a macro button export the sheet to a tab-delimited file.
Currently I've the following script:
It works but the problem is that the date is not copied in the defined Format and that the currency sign is lost in several columns. Could you please help me to make the script complete?
Your effort will be very much appreciated!
Floris
I'm new to this Forum as to VBA and Macro's. I'mm hoping you can help me with the following since I'm not finding any copy/paste solution. Currently I'm working on an excel file that serves as input for InDesign. My challenge is to automate the following:
Excel formulas help me to fill a table that with a macro button export the sheet to a tab-delimited file.
Currently I've the following script:
Code:
Sub WriteToTextFile()Dim ExpRng As Range
Dim myTab As String
Open "/......./.....txt" For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]
Set ExpRng = Range("A1").CurrentRegion
FirstCol = ExpRng.Columns(1).Column
LastCol = FirstCol + ExpRng.Columns.Count - 1
FirstRow = ExpRng.Rows(1).Row
LastRow = FirstRow + ExpRng.Rows.Count - 1
For r = FirstRow To LastRow
Data = ""
For c = FirstCol To LastCol
If c = 1 Then myTab = "" Else myTab = vbTab
' Data = ExpRng.Cells(r, c).Value
If c = FirstCol Then
Data = Data & ExpRng.Cells(r, c).Value
Else
Data = Data & vbTab & ExpRng.Cells(r, c).Value
End If
Next c
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , Data
Next r
Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]
MsgBox "file has been generated"
End Sub
It works but the problem is that the date is not copied in the defined Format and that the currency sign is lost in several columns. Could you please help me to make the script complete?
Your effort will be very much appreciated!
Floris