VBA/Macro help

[bolwahnn]

Hey Guys,

I currently receive a CSV report in excel that contains "site ID's". Example of these Site ID's are: K7, CK055, CK056, NOV07, etc, etc.

The problem is that all the site ID's that contain NOVxx are turned into a date. So, for instance, site ID NOVO7 is changed to 11/7/2018 and is formatted to show 07-Nov.

Everyday, I must manually filter all the sites that showed up as dates (so the November dates), change the date to text (07-Nov turns into 43411) and type in the correct site ID (NOV07).

Is there a macro that I could run that would scan the entire column and do this for me? Sometimes its only a few changes needed, but other times it could be up to 20.

 

[Joe4]

The issue is in how you are opening the file.

Microsoft has decided that Excel is the default program to open CSV files - this is a horrible idea, because it does some automated conversions as it "guesses" at the data types and doesn't let you intervene. Sometimes it guesses wrong, as you are seeing.

The key is to open it differently. Go into Excel directly. Then, go to Data -> Get External Data -> From Text and select the CSV file you want to open.
This will invoke the Import Wizard where YOU get to determine the data type of each field. On Step 3, you can select each field, and pick the correct data type. For this field in question, be sure to select "Text". Then it will come in the way that you want.

Note that you can automate this process by turning on the Macro Recorder and recording the the steps you perform manually to do this.
If the file is named differently every day, we can help you make it more dynamic so this Macro will allow you to browse for the file and then open it the way you need.
If you would like that, just post your recorded code here and we can help with that.

 

[bolwahnn]

Thank Joe4,
I was actually going to go down this path so that I could try and automate the report. I was able to get our IT guys to drop the CSV files in a network folder so that I could pull the data in doing the steps you suggested Data -> Get External Data -> From Text.

I've now ran into a different issue (I'm not sure of how to fix). When using the import wizard on step 2, I'm unable to get the data properly formatted when selecting the delimiters. I noticed that on step 1 of the import the file origin is showing 65001 : Unicode (UTF-8). This is different from other files that I've done this for which usually has 437 :OEM..... Not sure if this has anything to do with it but, its the only thing I noticed that was different.

 

[offthelip]

If you want to change dates to the text string NOV07 then this routine will do it for you:

Sub test2()Dim inarr, cl As Range
Set inarr = Range("I1:I26")
  For Each cl In inarr.Cells
   If IsDate(cl.Value) Then
   tx1 = MonthName(Month(cl.Value), True)
   tx2 = CStr(Day(cl.Value))
   If Len(tx2) = 1 Then
   tx2 = "0" & tx2
   End If
    cl.Value = "'" & tx1 & tx2
   End If
  Next cl
  
End Sub

I assume you list was in Column I1 to 26 , obviously change this to the range where it actually is

 

[bolwahnn]

If you want to change dates to the text string NOV07 then this routine will do it for you:

Sub test2()Dim inarr, cl As Range
Set inarr = Range("I1:I26")
  For Each cl In inarr.Cells
   If IsDate(cl.Value) Then
   tx1 = MonthName(Month(cl.Value), True)
   tx2 = CStr(Day(cl.Value))
   If Len(tx2) = 1 Then
   tx2 = "0" & tx2
   End If
    cl.Value = "'" & tx1 & tx2
   End If
  Next cl
  
End Sub

I assume you list was in Column I1 to 26 , obviously change this to the range where it actually is

Awesome! This worked to perfection! thanks!

 

[Joe4]

Just bear in mind that either way, you are running VBA code.
Personally, I would prefer to handle it on the incoming, to ensure nothing gets missed up (you are reading it in EXACTLY as it appears in the text file).

The other way you are converting a converted number back to the string it was originally (according to some programming rules). It SHOULD work, but there is a greater chance of things getting messed up, as you are doing a double-conversion.