How to keep set Data type in Excel sheet after importing Text data into excel using vba command?

[Porag]

Hi VBA Expert,
After importing Text data into excel using vba command how to keep the excel data type formatting cell which was set in Excel Active sheet, Like Text field, Numeric field, date field? Otherwise after importing the text data into excel automatically changed the excel cell format that is why Date format changing and another column 24 digits numeric value also changed like scientific symbol. How to fix it in VBA code Yours help and advise highly expecting.

 

[p45cal]

How are you importing the text?
Do you have some code which does this at the moment?
Perhaps attach or link to where the text is coming from, perhaps attach a workbook with the code in too?

 

[Porag]

H

How are you importing the text?
Do you have some code which does this at the moment?
Perhaps attach or link to where the text is coming from, perhaps attach a workbook with the code in too?

Hi p45cal,
Please find the attached macro enabled excel file with text data which will import into "JT_DATA" sheet and you can find my command button "Text Data import" which has invisible mode.

 

[p45cal]

I can see your txt file and am having a play with it, but your zip file isn't working; if it's a workbook you don't need to zip it up before attaching it here (unless it's really massive).

 

[p45cal]

The code below uses a legacy method of importing text files. It imports the dates in column 9 successfully as dates.
The dates are interpreted as YMD, decided by the 5 in the line:

  .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

Note in the first line the hard-coded path and filename - this is only proof of concept.

Sub Macro2()
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Users\Public\Downloads\22865_16741_16741_16741_JT_DK_CAN00004900127238_0725181920.00010_00010.txt", Destination:=Selection)
  .FieldNames = True
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .RefreshStyle = xlOverwriteCells    'xlInsertDeleteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .TextFilePromptOnRefresh = False
  .TextFilePlatform = 850
  .TextFileStartRow = 1
  .TextFileParseType = xlDelimited
  .TextFileTextQualifier = xlTextQualifierDoubleQuote
  .TextFileConsecutiveDelimiter = False
  .TextFileTabDelimiter = True
  .TextFileSemicolonDelimiter = False
  .TextFileCommaDelimiter = False
  .TextFileSpaceDelimiter = False
  .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
  .TextFileTrailingMinusNumbers = True
  .Refresh BackgroundQuery:=False
End With
End Sub

 

[Porag]

I can see your txt file and am having a play with it, but your zip file isn't working; if it's a workbook you don't need to zip it up before attaching it here (unless it's really massive).

Hi p45cal,
Please find the attached macro enabled excel file "JT_DATA" where you can find button Text Data upload which has invisible mode. I need this code for .txt file browsing

Thanks,
Porag

I can see your txt file and am having a play with it, but your zip file isn't working; if it's a workbook you don't need to zip it up before attaching it here (unless it's really massive).

Hi p4cal,
Please find the attached macro enabled "JT_DATA" excel file where you will find invisible mode command button "Text Data upload", I need .txt file import browsing option and need to fix the date field and 24 digits numeric value import without any scientific symbol.

Thanks,
Porag

 

[p45cal]

Right, in the attached, a new button calling CommandButton4_Click. It's exactly the same as your CommandButton2_Click except where you have:

Workbooks.OpenText _
            Filename:=fileToOpen, _
            StartRow:=1, _
            DataType:=xlDelimited, _
            Tab:=True

it has:

Workbooks.OpenText Filename:=fileToOpen, StartRow:=1, DataType:=xlDelimited, Tab:=True, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 5), Array(9, 5), Array(10, 2), Array(11, 2), Array(12, 1), Array(13, 2), Array(14, 2), Array(15, 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 1), Array(27, 2), Array(28, 2), Array(29, 2), Array(30, 2), Array(31, 2), Array(32, 2), Array(33, 2), Array(34, 2), Array(35, 2), Array(36, 2), Array(37, 2), Array(38, 2), Array(39, 2), Array(40, 2), Array(41, 2), Array(42, 2), Array(43, 2), Array(44, 2), Array(45, 2), Array(46, 2), Array(47, 2), Array(48, 2), Array(49, 2), Array(50, 2), Array(51, 2), Array(52, 2), Array(53, 2), Array(54, 2), Array(55, 2), Array(56, 2), Array(57, 2), Array(58, 2), Array(59, 2), Array(60, 2), Array(61, 2), Array(62, 2), Array(63, 2), Array(64, 2), Array(65, 2))

A new FieldInfo parameter added.
This took some doing because the microsoft documentation on Workbooks.OpenText here:

Workbooks.OpenText method (Excel)

Office VBA reference topic

docs.microsoft.com

when it discusses the FieldInfo part is plain WRONG.
Where it says

The column specifiers can be in any order. If there's no column specifier for a particular column in the input data, the column is parsed with the General setting.

It's not true. The column numbers are ignored. They're used in the order they appear in. So I've included the whole shooting match.

It's a real faff trying to write that FieldInfo parameter so, also in the attached, is a temporary sheet which will help us to construct the parameter.
The second member of each of the sub arrays is the data type which you can find at https://docs.microsoft.com/en-us/office/vba/api/excel.xlcolumndatatype
The bit which matters on Sheet1 is column D. You put the value (data type) you want in that column. I've used text for most (2), 1 (General) when there's a number you want as a number, and 5 (for YMD, year month day) in two of them.
Once you've adjusted that column D, you can copy the cell G3 in to the code.

I changed another line in your code from:

Sheet2.Range("A2:BM501").ClearContents

to:

Sheet2.Range("A2:BM501").Clear

which will clear formats too, since what you're now copying to that sheet already has its formats.

Column I (START_DATE) of JT_Data now contains proper Excel dates.
Column AQ (UPC_CODE) now shows all digits and no scientific number notaion.
Try it and tell me how you get on.

I'll also investigate incorporating my suggestion in msg#5 of this thread, as well as a Power Query solution.

 

[p45cal]

Attachment for previous message:

 

[Porag]

Hi p45cal,

Thanks a lot for great help and advise about my urgent solutions. Now working as per my requirements


Thanks
Porag