Getting sheet names from a second excel file using a macro. (Run-time error '9': Subscript out of range)

  • We had someone (I'm not sure) who make a data extractor in excel for the lab sometime before I joined this save much time when working with large data sets as I am sure you are aware. In the file labeled (Aga Lab Template_Data Extractor (for Xcalibur)) there is a cell that is filed with the text (ExcelExp_Long_Floc_BAC) this can be replaced with the name of your excel file to be extracted, (in testing I have changed the file name to "3" for simplicity) the macro seems to open the file and copy some of the cells from the extracted file to hidden sheets however when it attempted to access the excel file a second time there is an error
    [Blocked Image: https://mail.google.com/mail/u/1?ui=2&ik=2b3a882be6&attid=0.0.1&permmsgid=msg-a:r-7091154685300046057&th=177f439e0b002c68&view=fimg&sz=s0-l75-ft&attbid=ANGjdJ9nKMu8_ZRMxbvS2vsZbpJuoVSmZV5uowVB0Bwia6di72zYGCcBXgSsvaHYsaNhqfCXSj37ERoKuZiSjDZOJ2XfBM4MOPtRYRsSrTeHv76zJEvhzXIsdxIcyqs&disp=emb&realattid=ii_klscfqu80]

    I believe this means that it can not access the file but I am unsure why I have done some digging in the code but my VB knowledge is lacking.


    the code can be seen below and errors at (Workbooks(wb_data). Activate) in the ('GET SHEET NAMES) section


    Attached are the two files that are referenced above.

  • This line:

    Code
    1. Dim wb_main, wbm_path, wbm_title, wbm_ds, wbm_out, _
    2. wb_data, wbd_path As String


    only declares wbd_path as a String, all the rest are Variants. You need to change it to:


    Code
    1. Dim wb_main As String, wbm_path As String, wbm_title As String, wbm_ds As String, wbm_out As String, _
    2. wb_data As String, wbd_path As String


    and I think you'll find your problem will disappear.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • May I suggest that you try to help us to help you. What did you adjust?


    I'd suggest you use a variable for your workbook:


    Code
    1. Dim wb As Workbook
    2. Set wb = Workbooks.Open(wbd_path)


    then later you can refer to it directly:


    Code
    1. wb.activate


    not that you should need to select/activate anything here; it just slows down the code.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • I just added an enter after the As String that was suggested to enter in for some reason one space wasn't enough it needed two.


    I literally have no idea what I'm doing. I'm sorry I am of little help on this.


    This is the first time I have even looked at visual basic code.