How to pass a number as string argument in workbook object (excel)

Question

I am using a for loop to process data of 300 SKUs with some of them having SKU code as purely numeral. The raw data for each SKU is in separate file with both the workbook and worksheet name same as the SKU code. Error I am facing is index out of range as in:

Workbooks(wbnamex).Sheets(wbnamex).Cells(k, 2)

wbnamex contains SKU code and the ones as numerals are resulting in error. The object workbook is taking it as serial number rather than name.

How to go pass the purely numeral SKU code as String?

did you try using a string like this: " dim s as string", and then "s=wbnamex", Workbooks(s).Sheets(s).Cells(k, 2) — Tony M, Aug 22 '17 at 15:20
Possible duplicate of [How Do I Convert an Integer to a String in Excel VBA?](https://stackoverflow.com/questions/11595226/how-do-i-convert-an-integer-to-a-string-in-excel-vba) — Thomas Jager, Aug 22 '17 at 15:20
I believe you should be adding the file extension to the workbook's filename. Try `?thisworkbook.name` in the VBE's Immediate window. — , Aug 22 '17 at 15:22
Are workbooks opened? You must do `Workbooks.Open(xxx)` before using `Workbook(xxx)`. — LS_ᴅᴇᴠ, Aug 22 '17 at 15:26
@TonyM wbnamex was declared as String variable. The SKUs with codes such as S12345 are not giving error. Only something as 12345 are giving error. — Sagar Sachdeva, Aug 22 '17 at 15:32
@LS_ᴅᴇᴠ Two files are opened at a time within the loop, I guess this won't work. Anyhow Cstr () solved my problem — Sagar Sachdeva, Aug 22 '17 at 15:42

score 0 · Answer 1 · answered Aug 22 '17 at 15:37

This is something that will do the job:

Option Explicit

Public Sub TestMe()

    Dim strName     As String
    Dim wb          As Workbook
    Dim wsAny       As Worksheet
    Dim ws          As Worksheet

    strName = "Text"

    Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & strName & ".xlsm")

    For Each wsAny In wb.Worksheets
        If wsAny.Name = strName Then
            Set ws = wsAny
        End If
    Next wsAny

    Debug.Print ws.Cells(1, 1)

End Sub

You need an excel file named Text.xlsm with a Worksheet named Text as well. Then it will show you the Value of the A1 cell of this worksheet of this workbook.

There are other ways to do it, but this one works. The Text file should be in the same folder as the file, in which this code is present. The Excel file Text should be closed as well.

You can edit the code further, to make it work for other folders and with a file, that is already opened.

This code will help in another problem of mine. But for the original question passing the argument within Cstr() will solve the purpose. Eg.: Workbook(Cstr(wbname)).Sheets(Cstr(wbname)).cells (k, 10). Thanks anyways — Sagar Sachdeva, Aug 23 '17 at 05:50

How to pass a number as string argument in workbook object (excel)

1 Answers1