Column Formatting issue - VBA importing multiple CSV files into one Excel sheet.

[heathball]

This code works well and fits the purpose, but there is a problem column - "P" - the formatting of the text is, by example
76: 11-9-13

(maximum 3 digits in all 4 sections, always numbers, with a space after the colon)

I am looking for any pathway that does not end like this....

45050.46111 or 01-01-23 10.01

I'm thinking the solution must begin with an accurate custom format...

Unless there is a way to use both delimiters ( : - ) and separate the data into four number formatted columns, which could be placed to the right of the far right column (it is a fixed csv file, always containing 18 columns, ending at column R.)

A solution which ends in the correct text - through creative formatting - would also be a great result. (i have been toiling away at this for a while.)

Any help would be greatly appreciated.

Sub CombineCsvs()


Dim FolderPath As String
Dim FileName As String
Dim wbResult As Workbook
Dim WB As Workbook


  FolderPath = "D:\Aible\bin\Merge"
  If FolderPath Like "*[!\/]" Then
    FolderPath = FolderPath & "/"
  End If
 
  FileName = Dir(FolderPath & "*.csv")
 
  Set wbResult = Workbooks.Add
 
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
 
  Do While FileName <> vbNullString
    Set WB = Workbooks.Open(FolderPath & FileName)
    WB.ActiveSheet.UsedRange.Copy wbResult.ActiveSheet.UsedRange.Rows(wbResult.ActiveSheet.UsedRange.Rows.Count).Offset(1).Resize(1)
    WB.Close False
    FileName = Dir()
  Loop
 
  wbResult.ActiveSheet.Rows(1).EntireRow.Delete
 
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True

 
End Sub

 

[duggie33]

Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using, as the best solution often varies by version. Don’t forget to scroll down to save your changes.

Please provide some sample data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data, allowing us to copy/paste it to our Excel spreadsheets to work with the same data you are. As of October 2023, the latest version is 2.1. Instructions on using this tool can be found here: XL2BB Add-in

Note that this board also has a "Test Here” forum. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks,

Doug

 

[heathball]

Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using, as the best solution often varies by version. Don’t forget to scroll down to save your changes.

Please provide some sample data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data, allowing us to copy/paste it to our Excel spreadsheets to work with the same data you are. As of October 2023, the latest version is 2.1. Instructions on using this tool can be found here: XL2BB Add-in

Note that this board also has a "Test Here” forum. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks,

Doug

Thanks Doug,

Perhaps i am not clear about how my post is being read. I think my post is not clear.


I have updated my account details.

I have also installed XL2BB.

I will work on some extended examples of this code, with further attempts at solving it, and perhaps try a new post in the near future.

Thanks.