Excel taking time for Preserve format

Hi All,

I have an Excel which contains data like general, number and special character associated with data, and I need exactly the same format while reading.

I am able to achieve by enabling preserve format while read range activity but the concern is taking more time to read the excel.

Is there any chance or suggestion to improve reading the file, or how can do it better way…Pls help

Thank you all

@Sharanabasava1 can you upload the excel

@Sharanabasava1

  1. copy below code in text file e.g. vba.txt
  2. in excel application scope (before range range), use invoke vba activity

set function name = "convertSheetToTextFormat"
set entryMethodParameters = {"Sheet1"} (or change to your own sheet name)

  1. after invoke VBA , you can read range WITHOUT preserve format so it will be much faster but format will be the same
Function convertSheetToTextFormat(sheetName As String)

    ActiveWorkbook.Sheets(sheetName).Activate
    
    For i = 1 To 100
        Dim letter As String
        letter = ConvertNumberToLetter(CInt(i))
        Columns(letter & ":" & letter).Select
        On Error Resume Next
        
        Selection.TextToColumns Destination:=Range(letter & "1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 2), TrailingMinusNumbers:=True
        Next i
        
    ActiveWorkbook.Save
End Function


Function ConvertNumberToLetter(columnNumber As Integer)
    Dim letter
    letter = Split(Cells(1, columnNumber).Address, "$")(1)
    ConvertNumberToLetter = letter
End Function

Hi Jack

I am getting the below error

open blank excel

click options

click macro settings, tick these 2
image

Hi Jack,

In some of the column, vlookup formula is there…once read and write the data into another sheet…it look like this

image

only AN and AO have formula? is that correect ? ? / ? / / / / / / //?//?/ /////???

@Sharanabasava1 @Sharanabasava1 @Sharanabasava1

AM,AN, AO, AP column have Vlookup formula

change code to this

Function convertSheetToTextFormat(sheetName As String)

    ActiveWorkbook.Sheets(sheetName).Activate
    
    For i = 1 To 100
        Dim letter As String
        letter = ConvertNumberToLetter(CInt(i))
        
        If letter = "AM" Or letter = "AN" Or letter = "AO" Or letter = "AP" Then
            'skip
        Else
        
            Columns(letter & ":" & letter).Select
            On Error Resume Next
            
            Selection.TextToColumns Destination:=Range(letter & "1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                :=Array(1, 2), TrailingMinusNumbers:=True
            Next i
        End If
        
    ActiveWorkbook.Save
End Function


Function ConvertNumberToLetter(columnNumber As Integer)
    Dim letter
    letter = Split(Cells(1, columnNumber).Address, "$")(1)
    ConvertNumberToLetter = letter
End Function

One thing I observed is while reading the data, it got changed to

image

yes yes, do you have a back up of that file you can use? because VBA changes the format to text

use a backup of that file, and run the VBA again , it will skip AN-AP columns

Yes I have backup, but I want to read column AN-AP also

yes just use the backup file and run it … it will read those columns as well but wont read as text …

use this

Function convertSheetToTextFormat(sheetName As String)

    ActiveWorkbook.Sheets(sheetName).Activate
    
    For i = 1 To 100
        Dim letter As String
        letter = ConvertNumberToLetter(CInt(i))
        
        If letter = "AM" Or letter = "AN" Or letter = "AO" Or letter = "AO" Then
            'skip
        Else
        
            Columns(letter & ":" & letter).Select
            On Error Resume Next
            
            Selection.TextToColumns Destination:=Range(letter & "1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                :=Array(1, 2), TrailingMinusNumbers:=True
        End If
    Next i
        
    ActiveWorkbook.Save
End Function


Function ConvertNumberToLetter(columnNumber As Integer)
    Dim letter
    letter = Split(Cells(1, columnNumber).Address, "$")(1)
    ConvertNumberToLetter = letter
End Function

image

Columnn contains #N/A value but I am getting exponential value

image

Hi Jack,

Can you pls help on this…need as #N/A not the exponential value