lundi 20 avril 2015

Efficiently transfer Excel formatting data to text file

Here it is, I have a huge Excel workbook with which users write pricing quotes. On save, rather than saving the huge workbook, I'm transferring the relevant data to a text file and saving that text file. It's going off without a hitch, except for the one worksheet that contains formatting. I don't want the user to lose formatting when they load the previously saved quote (from the text file), so I need to determine a way to transfer that formatting data to and from the text file. Is there a smart way to do this without writing hundreds of lines of code or using any non-native Excel feature?

Here's a sample of the code for other sheets, but it's not much help for what I'm trying to do:

Sub WriteQuote()

Dim SourceFile As String
Dim data As String
Dim ToFile As Integer
Dim sh1, sh2, sh3 As Worksheet

Set sh1 = Sheets("sheet 1")
Set sh2 = Sheets("sheet 2")
Set sh3 = Sheets("sheet 3")

SourceFile = "C:\Users\███████\Desktop\test.txt"
ToFile = FreeFile

Open SourceFile For Output As #ToFile

For i = 7 To 56
If sh1.Range("B" & i).Value <> "" Then
    data = sh1.Range("B" & i).Value & "__"
    If sh1.Range("D" & i).Value <> "" Then
        data = data & sh1.Range("D" & i).Value & "__"
    Else: data = data & " __"
    End If
    If sh1.Range("E" & i).Value <> "" Then
        data = data & "ns" & "__"
    Else: data = data & " __"
    End If
    data = data & sh1.Range("F" & i).Value & "__"
    data = data & sh1.Range("G" & i).Value & "__"
    data = data & sh1.Range("J" & i).Value & "__"
    data = data & sh1.Range("M" & i).Value
Else: Exit For
End If
Print #ToFile, data
Next i
Close #ToFile
End Sub

Aucun commentaire:

Enregistrer un commentaire