MID function does nothing when run from VBS
I've been trying all day to do something pretty simple. I'm an absolute newbie with VB, so I'm pretty sure I missed something.
I am trying to use MID
to split numbers in a table column.
Here's what I have so far (I've only tried 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 app opens, the worksheet is active, then ... nothing. There is no mistake or anything else. It seems to literally stop there and ignore the last line altogether. The numbers I want to split looks like this in Excel. They are only dates that are backward, hence they want to separate, so I can separate and put them back on track.
20140101
20140127
20140303
20140310
20140310
20140310
20140310
20140418
20140419
Any help is greatly appreciated!
source to share
Try the following:
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
source to share
You can use Mid
in vbscript. This is not the problem. The problem is A1
in the line sub_str = Mid(A1, 1, 4)
.
A1
treated like a variable. It's always best to work with objects. Also if you want to 20140419
be changed to 04192014
, you really don't need to Mid
. You can use Left
it Right
for that too.
I am assuming the data is in format yyyymmdd
and you want the result to be like mmddyyyy
. If you want the result to work like ddmmyyyy
you will need 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
source to share
The Mid function is not a VBscript function, but a VBA function, so this might work anyway:
objExcel.Mid([A1], 1, 4)
instead of this:
Mid(A1, 1, 4)
If that doesn't work, you need to start Excel, then try putting all the logic in an Excel function and executing it from VBscript if necessary (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
source to share