Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Execute VBA statement for active workbook
#1
The objetive is to separate text concentred in column A in A B C which is separeted by space
Need to execute this line native from VBA from QM to the active worksheet:
Code:
Copy      Help
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True

This is the full code:
 
Code:
Copy      Help
typelib Excel
Excel.Application a._create
Excel.Workbook b=a.Workbooks.Add()
ExcelSheet es.ws=b.Worksheets.Item(1)
es.Activate(4) ;;activate Excel
es.ws.Application.Sheets.Item(1).Name = "Plan de Trabajo y Retorno del C"
int w=win("" "XLMAIN")
max w
str f.expandpath("$desktop$\test45.xls")
del f; err
b.SaveAs(f @ @ @ @ @ 1)
es.ws.Application.ActiveWindow.Zoom=85

  Hoja 1
es.ws.Application.ActiveSheet.Range("A1:A5").Value = "XXX YYY ZZZ"
es.ws.Application.ActiveSheet.Range("A1:A5").Select
str code=
 Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
VbsExec code

The result should be: Range A1:A5 = XXX, Range B1:B5 = YYY, Range C1:C5 = ZZZ... I tought that VbsExec could make it like this way but dont know how, please help  Huh
#2
Objective reached not precisely through VBA but VBS:
 
Code:
Copy      Help
 
typelib Excel
Excel.Application a._create
Excel.Workbook b=a.Workbooks.Add()
ExcelSheet es.ws=b.Worksheets.Item(1)
es.Activate(4) ;;activate Excel
es.ws.Application.Sheets.Item(1).Name = "Plan de Trabajo y Retorno del C"
int w=win("" "XLMAIN")
max w
str f.expandpath("$desktop$\test45.xls")
del f; err
b.SaveAs(f @ @ @ @ @ 1)
es.ws.Application.ActiveWindow.Zoom=85

  Hoja 1
es.ws.Application.ActiveSheet.Range("A1:A5").Value = "XXX YYY ZZZ"
es.ws.Application.ActiveSheet.Range("A1:A5").Select
int ultima_fila = es.ws.Application.ActiveSheet.Range("A1").End(xlDown).Row
out ultima_fila
VbsAddCode ""
out VbsFunc("Func1" f ultima_fila)



es.ws.Application.ActiveSheet.Range(F"C1:X{ultima_fila}").Clearcontents
es.ws.Application.ActiveSheet.Range(F"C1:C{ultima_fila}").Value = "=RC[-2]&RC[-1]"
  Tamano de columnas
 Range ra=es.ws.Application.Range("A1")
 ra.Font.Name="Arial"


#ret
'VBScript code

Function Func1(ByVal a, byval b)
'MsgBox b
'Func1=a*2
set wb = GetObject(a)
wb.sheets(1).range("c7").value = "loquesea"
wb.sheets(1).select
wb.sheets(1).Range("A1:A" & b).TextToColumns wb.sheets(1).Range("A1"),1,1,0,0,0,0,0,1," "
End Function
#3
A cleaner way:
 
Code:
Copy      Help
 
typelib Excel
Excel.Application a._create
Excel.Workbook b=a.Workbooks.Add()
ExcelSheet es.ws=b.Worksheets.Item(1)
es.Activate(4) ;;activate Excel
es.ws.Application.Sheets.Item(1).Name = "Plan de Trabajo y Retorno del C"
int w=win("" "XLMAIN")
max w
str f.expandpath("$desktop$\test45.xls")
del f; err
b.SaveAs(f @ @ @ @ @ 1)
es.ws.Application.ActiveWindow.Zoom=85


  Hoja 1
es.ws.Application.ActiveSheet.Range("A1:A5").Value = "XXX YYY ZZZ"
es.ws.Application.ActiveSheet.Range("A1:A5").TextToColumns(es.ws.Application.Range("A1"),1,1,1,0,0,0,0,1," ")


Forum Jump:


Users browsing this thread: 1 Guest(s)