WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
OLE with Excel
plus
Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Showing the Value of Excel Dynamic Ranges

If you anticipate ever having to use SQL to Select data from Excel, using Named Ranges is easiest, especially when dealing with multi-tab workbooks.

In situations where the data in the named range may increment or decrement, you would want a method to dynamically adjust the range (as opposed to manually changing it). One solution is to create a self-adjusting range at the outset.

The following script and two input text files illustrate the concept. And you can get tricky and include dynamic ranges within dynamic ranges which permits writing formulas which adapt as the range changes.

Stan 

 ; Winbatch 2004F - Excel Dynamic Range
;                  Simple Example
;
; You may come across the need to assign a range name that
; adapts to the number of rows of actual data - especially if you
; are looping in array values and constantly clearing cells
; or if you are importing csv data over time
;
; Stan Littlefield, November 11, 2004 [Please retain this header]
;///////////////////////////////////////////////////////////////////////

GoSub udfs
IntControl(73,1,0,0,0)
xlUp = -4162
path = DirScript()
cFile = StrCat(path,"Drange.txt")
If ! FileExist( cFile )
   Display(1,"Input File Missing",cFile)
   Exit
EndIf
cFile1 = StrCat(path,"Drange1.txt")
If ! FileExist( cFile1 )
   Display(1,"Input File Missing",cFile1)
   Exit
EndIf
BoxOpen("Please Wait","Creating Dynamic Range and Inserting Data")

oXL = CreateObject("Excel.Application")
If oXL == 0 Then Exit
oXL.Visible          = 1
oXL.ScreenUpdating   = 1
oXL.UserControl      = 1
oXL.DisplayAlerts    = 0
oXL.WorkBooks.Add()
BoxShut()

oWS = oXL.ActiveWorkbook.Worksheets(1)
oWS.Activate()
sName = oWS.Name
wName = "MyRange"
oWS.Cells(1,1).Activate()
Message("Step 1",StrCat("Create Dynamic Range Name = MyRange",@CRLF,'oXL.ActiveWorkbook.Names.Add(::Name="%wName%",RefersTo="=OFFSET(%sName%!$A$1,0,0,COUNTA(%sName%!$A:$A),COUNTA(%sName%!$1:$1))" ))' ))
oXL.ActiveWorkbook.Names.Add(::Name="%wName%",RefersTo="=OFFSET(%sName%!$A$1,0,0,COUNTA(%sName%!$A:$A),COUNTA(%sName%!$1:$1))" )

Message("Step 2","Input Data from %cFile%")
ClipPut("")
ClipPut(StrReplace(FileGet(cFile),",",@TAB))
oWS.Paste(oWS.Cells(1,1))

Message("Step 3",StrCat("Find Next Blank Row and",@CRLF,"Input Data from %cFile1%"))
NextRow = oWS.Range("A65536").End(xlUp).Row+1
ClipPut("")
ClipPut(StrReplace(FileGet(cFile1),",",@TAB))
oWS.Paste(oWS.Cells(NextRow,1))
Message("Final Step",StrCat("Issue This Command",@CRLF,'oWS.Range("MyRange").Clear()'))
oWS.Range("MyRange").Clear()
Display(2,"","End of Demo")

oWS=0
oXL.Quit()
oXL=0

Exit


:WBERRORHANDLER
IntControl(73,1,0,0,0)
oWS=0
oXL=0
ErrorProcessing(1,1,0)
Exit

:udfs
#DefineSubRoutine ErrorProcessing(deleteIni,showerr,logfile)
WbError = LastError()
WbTextcode = WbError
If WbError==1668||WbError==2669||WbError==3670
   ; 1668 ; "Minor user-defined error"
   ; 2669 ; "Moderate user-defined error"
   ; 3670 ; "Severe user-defined error"
   WbError = ItemExtract(1,IntControl(34,-1,0,0,0),":")
   WbTextcode = -1
EndIf
WbErrorString = IntControl(34,WbTextcode,0,0,0)
WbErrorDateTime = TimeYmdHms()
WbErrorFile = StrCat(DirWindows(0),"WWWBATCH.INI")
If deleteIni
   FileDelete(WbErrorFile)
   IniWritePvt(WbErrorDateTime,"CurrentScript",WbErrorHandlerFile      ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ErrorValue"   ,WbError                 ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ErrorString"  ,WbErrorString           ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ScriptLine"   ,WbErrorHandlerLine      ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ScriptOffset" ,WbErrorHandlerOffset    ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"VarAssignment",WbErrorHandlerAssignment,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"VarInSegment" ,WbErrorInSegment,WbErrorFile)
   IniWritePvt("","","",WbErrorFile)
EndIf
WbErrorMsgText = StrCat(WbErrorDateTime,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Current Script: ",WbErrorHandlerFile,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error# [",WbError,"]",@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error Text: ",wberrortextstring,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"[Extended Information] ",wberroradditionalinfo,@CRLF,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"On Line:",@CRLF,WbErrorHandlerLine,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Offset: ",WbErrorHandlerOffset,@CRLF)
If (WbErrorHandlerAssignment>"") Then %WbErrorHandlerAssignment% = "UNKNOWN"
WbErrorMsgText = StrCat(WbErrorMsgText,"Assignment/Variable: ",WbErrorHandlerAssignment,@CRLF)
If (WbErrorInSegment>"") Then WbErrorMsgText = StrCat(WbErrorMsgText,"In UDF/UDS: ",WbErrorInSegment,@CRLF)
If logfile
   cSep = StrCat(StrFill("=",50),@CRLF)
   cLogFile = StrCat(DirScript(),"log.err")
   If ! FileExist(cLogFile) Then FilePut(cLogFile,StrCat("Error Log",@CRLF,cSep))
   FilePut(cLogFile,StrCat(FileGet(cLogFile),WbErrorMsgText,cSep))
EndIf
If showerr Then Message("wbErrorHandler",WbErrorMsgText)
Return(1)
#EndSubRoutine

Return
;/////////////////////////////////////////////////////////////////////////////////////

DRANGE.TXT 

100,110,120,139,151
206,200,179,400,116
178,300,315,318,209

DRANGE1.TXT 

106,210,403,190,111
309,200,215,165,398
178,124,227,300,119
Article ID:   W17162

File Created: 2007:07:03:14:28:34
Last Updated: 2007:07:03:14:28:34