Help, How to set Right and Left function for different columns??

[Ftwtreefiddy]

>>> use code - tags <<<

Sub ExportFixedLength1()
    Dim A As String * 30, B As String * 25, C As String * 20, D As String * 15, E As String * 10
    Dim FF%, Rg As Range
        FF = FreeFile
        Open ThisWorkbook.Path & "\ExportFL 1 .txt" For Output As #FF
    For Each Rg In ActiveSheet.UsedRange.Rows
         A = Rg.Cells(1).Text
         B = Rg.Cells(2).Text
         C = Rg.Cells(3).Text
         D = Rg.Cells(4).Text
         E = Rg.Cells(5).Text
         Print #FF, A; B; C; D; E
    Next
         Close #FF
End Sub

Using this to print a text file with fixed spacing but I need to make it so column C(3) will extract the text from the right not the default left?? Anytime I've tried to add the Right function this is an exapmle :

Dim Myvalue As String
Dim Myvalue
Myv = Right(C, 20)

Or anything similar I get an error?

 

[Ftwtreefiddy]

Sub ExportFixedLength2n()

        Dim V, L%, P%(), C%, F%, Rg As Range, S

            V = [{30,25,20,15,10,5}]

            L = 1

            ReDim P(1 To UBound(V))

            For C = 1 To UBound(V):  P(C) = L:  L = L + V(C):  Next

            Application.UseSystemSeparators = False

            F = FreeFile

            Open ThisWorkbook.Path & "\ExportFL 2n .txt" For Output As #F

    For Each Rg In ActiveSheet.UsedRange.Rows

        For C = 1 To UBound(V)

            S = Right(Rg.Cells(C).Text, V(C))

            Print #F, Tab(P(C) - (V(C) - Len(S)) * IsNumeric(Rg.Cells(C).Value)); S;

        Next

            Print #F, Tab(L)

    Next

            Close #F

            Application.UseSystemSeparators = True

End Sub



Above works just as well and I can set all text to pull from the Right but can't have some from the left and some from the right? Any help?

 

[Marc L]

Unclear …​

​

So read again my thread post #8 in particular :​

Two Old School Ways To Export Cells To A Fixed Length Text File

Yes I know : it's not fancy but I saw from time to time always the same subject : how to … Yes I know : many samples on web whatever in forums or blogs, so I thought of some lazy people requering to do their job ! But recently I saw a blog on this subject, a nice looking webpage but so ugly is...

chandoo.org

​

As a reminder any smart import feature does not need numbers to be right formatted …​

​

For further help attach at least a sample source workbook and accordingly the expected export text file​

without forgetting to indicate the maximum length of each exported column.​

​

 

[Ftwtreefiddy]

I'm using the code you put together, Thanks by the way! It works great however, I need to set it up, so column E pulls from the right when printing. As all columns already pull from the left. I need this because I have formatted E and H to have four "0"'s to the left and right respectively(could be 30 or 100 zeros) these zeros represent the decimal place where E is a whole number and H has four zeros after the decimal. If the input values change from 1, 2 or 3 digits, I want the zeros to drop off as the text file is produced same as it does currently for column H. That way all the spacing is held as the spacing denotes where this data will go in our database when we import the text file.

Hope this makes sense see the difference between column E and H on row 5 where I change the input value from 1 to 111 on E and 20 to 222 on H. The text produced is correct for H5 but not for E5. When output to text E1-E4 is correct 00001, E5 is not (it matches E1-E4) it should be 00111. E5 dropped two of the values and kept two of the placeholder zeros. Check H5 it matches H1-H4 as it should, due to the fact it drops all zeros from the right because it pulls the first 6 spaces from the left and drops everything else. I would like to be able to set different rows as necessary to either pull from the left or right and drop the placeholders thus keeping the set lengths of each column.


If ExportFixedLength1(What I’m using) is too rudimentary for that type of functionality. I am able to work with ExportFixedLength2n as well and have already been able to set it from right to left but only for the entire sheet not by comlumn. So if you need to use code more like ExportFixedLength2n I can work with it. If it's not obvious, it's my first time working with code as I'm an electircal engineer but desperatly need this for a strange project I found myself on...

 

[Ftwtreefiddy]

From	To	Field	Len	Dec	Typ	Text	Required​	Example​
1	20	R	20		A	POT ID	​	521-01720479-MWW​
21	40	RCT	20		A	NUM	​	​
41	60	RC	20		A	BIL	​
61	80	RCP	20		A	PA	​	​
81	85	RCP	5	0	S	POT	​	00005​
86	105	RC	20		A	IT	​	477794A​
106	155	RCP	50		A	EM	​	24-100P SOL BC PE-89 TYPE REA FOAMFILLED,BLK LLDPE​
156	168	RCR	6	4	S	REC	​	660000​
169	180	RC	12		A	STOR	​	1​
181	188	RCR	8		A	STO	​	​
189	200	RCRD	12		A	ST	​	​
201	208	R	8	0	S	PROC	​	​
209	214	RCP	6	0	S	PRO	​	​

File B to A
Above is the output requirments I'm working with for one data set... Everything pulled from right example attached using Exportfixedlength2n, now colummn H is wrong but E is correct.

 

[Marc L]

According to post #5 check this demonstration :​

Sub Demo2n()
        Dim V, L%, T$, P%(), C%, F%, Rg As Range, S
            V = [{20,20,20,20,5,20,50,13,12,8,12,8,6}]
            L = 1
            T = String(V(5), "0")
            ReDim P(1 To UBound(V))
            For C = 1 To UBound(V):  P(C) = L:  L = L + V(C):  Next
            Application.UseSystemSeparators = False
            F = FreeFile
            Open ThisWorkbook.Path & "\File E – RgtInbound Receipt – Response to File A .txt" For Output As #F
    For Each Rg In ActiveSheet.UsedRange.Rows
            S = Rg.Value2
            S(1, 5) = Format$(S(1, 5), T)
            S(1, 8) = S(1, 8) & "0000"
        For C = 1 To UBound(V)
            S(1, C) = Left(S(1, C), V(C))
            Print #F, Tab(P(C) - (V(C) - Len(S(1, C))) * IsNumeric(S(1, C))); S(1, C);
        Next
            Print #F, Tab(L)
    Next
            Close #F
            Application.UseSystemSeparators = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Ftwtreefiddy]

While adapting this for my other data sheet I get the (error: subscript out of range) as soon as I add a 20th column to V??

Can't adapt it fully for the below data...

 

[Marc L]

'Cause obviously only 19 columns are used so at least some data must be in the 20th column​

or just do not forget to loop on the columns used rather than on the upper bound of the array variable V​

or just adapt the souce range on 20 columns rather than using the property UsedRange !​

​