SEE BELOW CODE.
The snippet seen is being applied to an excel file (only provided to show how I'm accessing the file). I have 2 columns I need to clean up. The listed code will accomplish most of what I want, but I have 1 other issue: I have a column in this sheet (column D) that contains location codes. Location codes are typically numbers, except for 1 code: 005-1. Based on how my database is set up and coded, I can't accept that value (hence my need to clean columns). I need to change 005-1 to 005 (or just 5 will be fine). I can't find the right syntax to express this. Basically I want:
If ColumnD Value = "005-1" then change to "005" (or "5")
But understand, this will be run from a form created in VB using Visual Studio, so I think I will need syntax different then the standard Excel. I'm sure this is a lot easier than I'm making it. I would appreciate any help or hint on this.
xlObj = CreateObject("Excel.Application")
xlObj.Visible = False
xlBook = xlObj.Workbooks.Open(strFileName)
xlSheet = xlBook.Worksheets("HOURLY")
xlSheet.Range("J1", xlSheet.Range("J" & xlSheet.Rows.Count).End(-4162)).Offset(1, 0).Copy()
xlSheet.Range("K1").Value = "Units"