[RESOLVED] [VB6] Using Variables with objWS.Range.BorderAround

[penticuf]

I am looking for assistance in using variables provided by a text file with the objWS.Range(start, end).BorderAround function. My issue is that I receive a "Type Mismatch" error with the current method I use. I am using VB 6 Enterprise edition and MS Excel 2000.

The purpose of my program is to create templates in a text file that VB will call to create a spreadsheet. This allows me to create new templates (text files) without hard coding them into my VB application.

The text file contains many lines but the one I am concerned with is:
ran,border,A4,G4,xlContinuous,xlThick,xlColorIndexAutomatic

The actual command that works if hard coded in VB is:

objWS.Range("A4", "G4").BorderAround xlContinuous, xlThick, xlColorIndexAutomatic

The code I am using that does NOT work is:

Public Function xlFeed(strInputFile As String, strOutputFile As String)
Dim i As Integer
Dim sngTemp As Single
Dim strLine As String
Dim strWord() As String
Dim objXL As Object
Dim objWB As Excel.Workbook
Dim objWS As Excel.Worksheet
Dim objRange As Excel.Range
 
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set objWB = objXL.Workbooks.Add()
Set objWS = objWB.Worksheets(1)
Open strInputFile For Input As #71
Do Until EOF(71)
    Line Input #71, strLine
    strLine = Pack(strLine)
    strLine = Trim(strLine)
    strWord = Split(strLine, ",")
    If UBound(strWord) >= 6 Then
        If LCase(strWord(0)) = "ran" Then
            If LCase(strWord(1)) = "border" Then
                objWS.Range(strWord(2), strWord(3)).BorderAround strWord(4), strWord(5), strWord(6)
            End If
        End If
    End If
Loop
objWB.ActiveSheet.SaveAs (strOutputFile)
objWB.Close
objXL.Quit
Set objRange = Nothing
Set objWS = Nothing
Set objWB = Nothing
End Function

strInputFile is the text file; strOutputFile is a new Excel spreadsheet. When I comment out the tail of the command in question the error disappears but I do not get the border I want:

objWS.Range(strWord(2), strWord(3)).BorderAround 'strWord(4), strWord(5), strWord(6)

Any help would be greatly appreciated. Thank You,

[si_the_geek]

Welcome to VBForums

The problem is that the values like xlContinuous (and anything else starting with "xl") are constants that contain numbers, and you are passing strings (which happen to contain the names of the constants).

I don't know if there is a way to get the value of a constant using a string that contains its name - but as you are using late-binding, that wouldn't be an option anyway (because if it is possible, it would use the object library somehow).

There are a few alternatives I can think of ..

• Write a function that contains a massive Select Case, which does the conversion. This would be large and take a long time to create - and would be prone to mistakes/omissions.
• Instead of storing the name of the constant to the text file, store the value instead (and use CLng(strWord(4)) etc). This would make it harder to read/update tho.
• Instead of storing info about the template and creating the file each time.. simply store a pre-created file, which you then copy.

In most cases, I would pick the last option.

[penticuf]

Si,

Thanks for the information and thanks for the welcome! I have decided to place the numeric values in my external template files. I only use a few of the xlNAME parameters and is easy to do. I am amazed at the lack of information on the numeric values of constants on the web. I looked forever until I figured out I could retrieve them with a simple "msgbox xlContinuous"! As you can tell I am relatively new to VB and still learning. Again thanks for your time and response.

[si_the_geek]

No problem.. a quicker alternative to the MsgBox idea is to go to the Immediate window (press Ctrl-G in the VB editor), then enter this:

? xlContinuous

..and press enter - the value will be printed to the next line (as the values of some constants are like -4302 , being able to copy+paste them is nice!).