MID function does nothing when run from VBS

Question

I have been trying all day to do something pretty simple. I am an absolute newbie with VB so I am sure I've missed something.

I have been trying to use MID to split up the numbers in a column on a spreadsheet.

Here is what I have so far (I have been trying to do only one to make sure it works):

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\<username>\Desktop\New.csv")

Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate

sub_str = Mid(A1, 1, 4)

So the application opens, the worksheet is active, then... nothing. No error or anything. It's like it literally stops there and ignores the last line altogether. The numbers that I want to split look like the below in Excel. They are just dates that are backwards, hence the wanting to split, so I can separate and put it the right way round.

20140101

20140127

20140303

20140310

20140310

20140310

20140310

20140418

20140419

Any help is very appreciated!

Answer 1

Try this:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\USER\Desktop\new1.csv")

Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate
rowCount=objWorksheet.usedrange.rows.count
Set rngA=objWorksheet.Range("A1:A" & rowCount)
'Set rngB=rngA.offset(,1)             'objWorksheet.Range("B1")


with objWorksheet
for each cell in rngA
sub_strY = Mid(cell.value, 1, 4)
sub_strM=Mid(cell.value, 5, 2)
sub_strD=Mid(cell.value, 7, 2)
'msgbox sub_strY
'msgbox sub_strM
'msgbox sub_strD
strDate=sub_strD & "/" & sub_strM & "/" & sub_strY
msgbox strDate
'cell.offset(,1).value=strDate     ''to another column  
cell.value=strDate                 ''to overwrite
next
end with

Answer 2

You can use Mid in vbscript. The problem is not there. The problem is in A1 in the line sub_str = Mid(A1, 1, 4).

A1 has been treated like a variable. It's always best to work with objects. Also if you want 20140419 to be changed to 04192014 then you actually do not need Mid. You can use Left and Right for this.

I am assuming that the data is in the format yyyymmdd and you want output as mmddyyyy. If you want the output as ddmmyyyy, then you will have to use Mid. like this

sDate = Right(.Range("A" & i).Value, 2) & _
        Mid(.Range("A" & i).Value, 5, 2) & _
        Left(.Range("A" & i).Value, 4)

Is this what you are trying?

Const xlUp = -4162

Dim oXLApp, oXLwb, oXLws
Dim lRow, i
Dim sFile, sDate 

'~~> Change this to the relevant file
sFile = "C:\Users\Siddharth Rout\Desktop\book1.xlsx"

'~~> Establish an EXCEL application object
On Error Resume Next
Set oXLApp = GetObject(, "Excel.Application")

'~~> If not found then create new instance
If Err.Number <> 0 Then
    Set oXLApp = CreateObject("Excel.Application")
End If
Err.Clear
On Error GoTo 0

'~~> Hide Excel
oXLApp.Visible = False

'~~> Open files
Set oXLwb = oXLApp.Workbooks.Open(sFile)

'~~> Set the worksheet you want to work with
Set oXLws = oXLwb.Sheets(1)

'~~> work with the worksheet
With oXLws
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row

    For i = 1 To lRow
        '~~> 20140101 becomes 01012014. If the Col A is formatted 
        '~~> as number then the leading `0` will disappear as 
        '~~> shown in the screenshot below
        sDate = Right(.Range("A" & i).Value, 4) & Left(.Range("A" & i).Value, 4)
        .Range("A" & i).Value = sDate
    Next
End With

'~~> Close and save
oXLwb.Close (True)

'~~> CLEANUP (VERY IMPROTANT)
Set oXLws = Nothing
Set oXLwb = Nothing
oXLApp.Quit
Set oXLApp = Nothing

MsgBox "DONE" 'OR wscript.echo "Done"

Screenshots:

Before

After

Answer 3

The Mid function is not a VBscript function but a VBA function therefore if anyway this might work:

objExcel.Mid([A1], 1, 4)

instead of this:

Mid(A1, 1, 4)

If this does not work a you need to run Excel then try putting all the logic in the Excel function and executing it from VBscript if needed (example below):

RunMacro
Sub RunMacro() 
  dim xl
  Set xl = CreateObject("Excel.application")
  Dim xlBook      
  Dim sCurPath
  path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
  Set xl = CreateObject("Excel.application")
  Set xlBook = xl.Workbooks.Open(path & "\Book1.xlsm", 0, True)      
  xl.Application.Visible = False
  xl.DisplayAlerts = False    
  xl.Application.run "Book1.xlsm!Module.MyMacro"
  xl.ActiveWindow.close
  Set xlBook = Nothing
  xl.Quit 
  Set xl = Nothing
End Sub