Macro VBA loop through, convert column to text then to general formatting

sryan801

New Member
Joined
Feb 21, 2019
Messages
3
Hi All,
I have written a code to try and format A:12 down to general from a date format. I have since discovered the code wasn't working as I need to use the "column to text" function. I have tried a few methods to get the code right (including recording) but when i actually run the macro it doesn't complete the "column to text" function and therefore won't convert to a general format.

Code:
Sub columntotext1()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them


Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog


'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual


'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)


    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & ""
    End With


'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings


'Target File Extension (must include wildcard "*")
  myExtension = "*.csv*"


'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)


'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
    
    'Ensure Workbook has opened before moving on to next line of code
   Range("A12").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("A12"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Selection.NumberFormat = "General"
    ActiveWorkbook.Save
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True


    'Get next file name
      myFile = Dir
  Loop


'Message Box when tasks are completed
  MsgBox "Task Complete!"


ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,503
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi syran801,

Welcome to the MrExcel Forum.

It is great that you have provided the code that you have so far.

Could you show us a sample of what a column or two of data might look like before the conversion and what you would like it to look like after the conversion.
 

sryan801

New Member
Joined
Feb 21, 2019
Messages
3
Hi igold,
Thanks for the reply

This is before:

------------ Data Table File Created By ActiGraph wGT3XBT ActiLife v6.12.1 Firmware v1.9.2 date format d/MM/yyyy Filter Normal -----------
Serial Number: MOS2D01180666
Start Time 15:00:00
Start Date 28/05/2018
Epoch Period (hh:mm:ss) 00:00:15
Download Time 16:35:34
Download Date 1/06/2018
Current Memory Address: 0
Current Battery Voltage: 3.95 Mode = 61
--------------------------------------------------
Date Time Axis1Axis2Axis3StepsLuxInclinometer OffInclinometer StandingInclinometer SittingInclinometer LyingVector Magnitude
28/05/201815:00:0000000000150
28/05/201815:00:1500000000150
28/05/201815:00:3000000000150
28/05/201815:00:4500000000150
28/05/201815:01:0000000000150
28/05/201815:01:1500000000150
28/05/201815:01:3000000000150
28/05/201815:01:4500000000150
28/05/201815:02:0000000000150
28/05/201815:02:1500000000150
28/05/201815:02:3000000000150
28/05/201815:02:4500000000150

<colgroup><col><col span="11"></colgroup><tbody>
</tbody>





and after

------------ Data Table File Created By ActiGraph wGT3XBT ActiLife v6.12.1 Firmware v1.9.2 date format d/MM/yyyy Filter Normal -----------
Serial Number: MOS2D01180666
Start Time 15:00:00
Start Date 28/05/2018
Epoch Period (hh:mm:ss) 00:00:15
Download Time 16:35:34
Download Date 1/06/2018
Current Memory Address: 0
Current Battery Voltage: 3.95 Mode = 61
--------------------------------------------------
Date Time Axis1Axis2Axis3StepsLuxInclinometer OffInclinometer StandingInclinometer SittingInclinometer LyingVector Magnitude
4324815:00:0000000000150
4324815:00:1500000000150
4324815:00:3000000000150
4324815:00:4500000000150
4324815:01:0000000000150
4324815:01:1500000000150
4324815:01:3000000000150
4324815:01:4500000000150
4324815:02:0000000000150
4324815:02:1500000000150
4324815:02:3000000000150
4324815:02:4500000000150
4324815:03:0000000000150
4324815:03:1500000150000
4324815:03:3000000150000
4324815:03:4500000150000
4324815:04:0000000150000
4324815:04:1500000150000

<colgroup><col><col span="11"></colgroup><tbody>
</tbody>


Apologies if there is a better way to show you this information - i couldn't seem to add the information into a table or use images.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,309
Office Version
  1. 365
Platform
  1. Windows
Judging by what you have posted the text to columns is working properly.
In xl dates are stored as numbers and the number 43248 represents 28/05/2018.
Are you sure you want that column as General, rather than Date
 

sryan801

New Member
Joined
Feb 21, 2019
Messages
3
Judging by what you have posted the text to columns is working properly.
In xl dates are stored as numbers and the number 43248 represents 28/05/2018.
Are you sure you want that column as General, rather than Date

Hi Fluff,

Yes I am positive the date must be stored as a number. This is important for the second macro that will be run on this data.

Thank you
 

Watch MrExcel Video

Forum statistics

Threads
1,108,990
Messages
5,526,096
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top