Excel taking time for Preserve format

Help Activities
, ,
1

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

3

@Sharanabasava1 can you upload the excel

4

@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)

image
image1154×510 25.6 KB

  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
5

Hi Jack

I am getting the below error

image

6

open blank excel

click options

image
image259×1020 6.88 KB

image
image1161×532 29.9 KB

click macro settings, tick these 2
image

7

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

9

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

@Sharanabasava1 @Sharanabasava1 @Sharanabasava1

10

AM,AN, AO, AP column have Vlookup formula

11

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
12

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

image

13

image
image688×545 37.8 KB

14

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

15

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

16

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

17

image
image1044×560 112 KB

18

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
19

image

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

20

image

21

Hi Jack,

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