1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Please HELP: Convert General datatype to Text datatype using VBA Macro

Discussion in 'Business Applications' started by cqn5008, Apr 27, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. cqn5008

    cqn5008 Thread Starter

    Joined:
    Mar 12, 2015
    Messages:
    16
    Hello Gurus,

    I am having some issue with the code for converting the General datatype to Text datatype using VBA Macro. What I am trying to do is to import the CSV file into Excel and some columns need to be changed to Text datatype to keep the leading zero(s). I tried the following code:

    Columns("T:T").NumberFormat = "@"

    It did convert the datatype from General to Text however it still dropped the leading zero(s).

    Some told me that I should try, for example:

    Columns("T:T").NumberFormat = "00" or
    Columns("T:T").NumberFormat = "0000" to display the leading zeros

    But not all the cells have the leading zero(s). For example,

    V896
    320
    430
    S890

    It should be

    V896
    0320
    430
    S890

    Any suggestions?

    Note: If I manually follow the importing process using Import Text under Data and choose the delimiter then change the datatype from there. It would work but not the codes above.
     
  2. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    You're essentially asking the VBA to return the leading zeros after the import process has "lost" them by not importing as the correct data type, which is why you are not having any luck.


    If you go through the wizard for importing you can see that it indicates that the "General" format will convert numeric values to number format, which is where the leading zeroes get discarded.
     
  3. cqn5008

    cqn5008 Thread Starter

    Joined:
    Mar 12, 2015
    Messages:
    16
    Hi David,

    Thanks for your quick response. So you are saying there is no way I can automate this process?
     
  4. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    Well you could automate the import process, including data type setting. It's just that after the data has been imported as "general", you've effectively discarded some useful information (which you are trying to reinstate).
     
  5. cqn5008

    cqn5008 Thread Starter

    Joined:
    Mar 12, 2015
    Messages:
    16
    Below is what I have and everything works but the last statement Columns("T:T").NumberFormat = "@"

    It converted it to Text type but the leading zeros are gone.


    Public Sub VBAMacroInpatient()
    '
    ' VBAMacroInpatient Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+I
    '
    Dim myFolder As String
    myFolder = "C:\CSV\" '* This is a variable to hold the folder name.
    ActiveSheet.Cells.Clear
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myFolder & "Inpatient.csv", _
    Destination:=Range("$A$1"))
    .Name = "Inpatient"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileOtherDelimiter = "-"
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    Columns("T:T").NumberFormat = "@"
    End With
    End Sub
     
  6. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    Your macro is changing the format of column T after the input.


    You need to specify that the data going into column T is text. You might have to mess about with it, but if it were just one column you would include:


    .TextFileColumnDataTypes = xlTextFormat


    or if you had more than one column you could use an array:


    .TextFileColumnDataTypes = _
    Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)



    The above is just an example from this link, based on three columns!

    https://msdn.microsoft.com/en-us/library/office/ff193261.aspx
     
  7. cqn5008

    cqn5008 Thread Starter

    Joined:
    Mar 12, 2015
    Messages:
    16
    Thanks for your response.

    There are actually more than 2000 columns so the Array might not work as it will give me the error message saying too many line continuations. Below is my entire code. I tried adding .TextFileColumnDataTypes = xlTextFormat to my code but it gave me the error message saying "Invalid procedure call or argument". I just inserted it under Columns("T:T").NumberFormat = "@". I have some Date fields. I am thinking about if it is possible to make every columns a text field and the code for FormatColumnJ() and FormatColumnK() would still work. If so, what would be my code be?

    Option Explicit

    Public Sub VBAMacroInpatient()
    '
    ' VBAMacroInpatient Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+I
    '
    Dim myFolder As String
    myFolder = "C:\CSV\" '* This is a variable to hold the folder name.
    ActiveSheet.Cells.Clear
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myFolder & "Inpatient.csv", _
    Destination:=Range("$A$1"))
    .Name = "Inpatient"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileOtherDelimiter = "-"
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    Columns("T:T").NumberFormat = "@"
    .TextFileColumnDataTypes = xlTextFormat
    End With
    End Sub

    Public Sub FormatColumnJ()
    Dim xCol As Long
    Dim Cell As Range
    Dim lstRow As Long
    lstRow = WorksheetFunction.Max(2, Cells(Rows.Count, "J").End(xlUp).Row)
    For Each Cell In Range(Cells(2, "J"), Cells(lstRow, "J"))
    With Cell
    .Value = DateSerial(Left(Cell.Value, 4), Mid(Cell.Value, 5, 2), Right(Cell.Value, 2))
    .NumberFormat = "yyyy/mm/dd"
    End With
    Next Cell
    End Sub

    Public Sub FormatColumnK()
    Dim xCol As Long
    Dim Cell As Range
    Dim lstRow As Long
    lstRow = WorksheetFunction.Max(2, Cells(Rows.Count, "K").End(xlUp).Row)
    For Each Cell In Range(Cells(2, "K"), Cells(lstRow, "K"))
    With Cell
    .Value = DateSerial(Left(Cell.Value, 4), Mid(Cell.Value, 5, 2), Right(Cell.Value, 2))
    .NumberFormat = "yyyy/mm/dd"
    End With
    Next Cell
    End Sub
    Sub RunMacro()

    Call VBAMacroInpatient
    Call FormatColumnJ
    Call FormatColumnK

    End Sub
     
  8. DaveBurnett

    DaveBurnett Account Closed

    Joined:
    Nov 11, 2002
    Messages:
    12,970
    Put quotes round the ones you want to retain the zeroes to turn them into text. Or format the field with a special format that retains leading zeroes.
     
  9. cqn5008

    cqn5008 Thread Starter

    Joined:
    Mar 12, 2015
    Messages:
    16
    Hello DaveBurnett,

    Thank you. Could you please explain the little more?
     
  10. DaveBurnett

    DaveBurnett Account Closed

    Joined:
    Nov 11, 2002
    Messages:
    12,970
  11. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1147337

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice