Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Dec 2003
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Help using VbScript to define Excel range

    Does anyone know how to define a range in Excel using vbScript?
    ANY help with this is MOST appreciated!

    thanks!

  • #2
    New Coder
    Join Date
    Jun 2005
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you mean a selection of cells in a similar row or column by "range", you would have to create an Excel object with VBScript and set it up appropriately. The bold code is how you would select a range, the rest is the creation of an Excel object in VBScript if you hadn't already done so:
    Code:
    Dim objXL
    Set objXL = WScript.CreateObject("Excel.Application")
    
    objXL.Visible = TRUE
    
    objXL.WorkBooks.Add
    
    objXL.Columns(1).ColumnWidth = 20
    objXL.Columns(2).ColumnWidth = 30
    objXL.Columns(3).ColumnWidth = 40
    
    objXL.Cells(1, 1).Value = "Column 1"
    objXL.Cells(1, 2).Value = "Column 2"
    objXL.Cells(1, 3).Value = "Column 3"
    
    objXL.Range("A1:C1").Select
    Hope this is what your looking for

  • #3
    New to the CF scene
    Join Date
    Dec 2003
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You are probably correct, but I am having problems getting it to work with my code.
    That gets me an error message saying that it could not find the range.

    I am trying to place the contents of the range into a recordset so I can post the values to an ASP page. If I name the range physically in Excel, the code works, but I cannot do that in all of the spreadsheets that folks will choose to display (preview) before they print.
    Here is the offending code:

    Code:
    Dim filePath, oExcel,objrange,oRangeName
    
    filePath = "K:\TrainingBudget.xls"
    Set oExcel = CreateObject("Excel.Application")
    oExcel.Workbooks.Open(filepath)
    oExcel.Visible = TRUE
    oRangeName = oExcel.Range("A1:C1").Select
    objrange = oRangeName
    
    strCmd = "SELECT * from objrange"
    Set oRS = Server.CreateObject("ADODB.Recordset")
    
    oRS.Open strCmd, oConn
    Thanks for the help. I really appreciate it.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •