Results 1 to 8 of 8

Using VBA to open a workbook and find the first blank row

This is a discussion on Using VBA to open a workbook and find the first blank row within the Excel Questions forums, part of the Question Forums category; Hi- I am trying to write some code in an excel workbook that will open another excel workbook and go ...

  1. #1
    New Member
    Join Date
    May 2004
    Posts
    27

    Default Using VBA to open a workbook and find the first blank row

    Hi-

    I am trying to write some code in an excel workbook that will open another excel workbook and go to the first blank line. I will then take some fields from the first workbook and insert them in appropriate columns in the second workbook.

    I am running into trouble trying to have VBA go to the first blank line in the second workbook that was open using VBA.

    Here is the code I have so far. The second workbook is opening but it is not making the first cell in the first blank row the active cell.

    Thanks!

    ........


    Private Sub CheckBox134_Click()

    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    'xlApp.ScreenUpdating = False
    Set xlWB = xlApp.Workbooks.Open("file.xls")
    'ActiveWorkbook.Close SaveChanges:=True
    ActiveWorkbook.Activate

    'Set oExcel = GetObject(, "Excel.Application")
    'With x1WB.ActiveSheet

    Sheets("file").Activate
    Selection.End(xlDown).Select


    End Sub

  2. #2
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: Using VBA to open a workbook and find the first blank ro

    Do you really mean the 1st empty row, or the next row down after the end of data?

    Is there a column which will always have content if there is anything at all on the row?
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  3. #3
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    Hi - Welcome to the board

    After you open the workbook put this

    Sheets("Sheet1").Activate 'Change as needed
    Range("A65536").End(xlUp).Offset(1,0).Select

  4. #4
    New Member
    Join Date
    May 2004
    Posts
    27

    Default Re: Using VBA to open a workbook and find the first blank ro

    Thanks. I get a "subscript out of range" with this code. The workbook opens as it should. Do I need to do something else to make it active?

    What is happening is excel is taking me to the first blank row of the initial workbook I have open.

  5. #5
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: Using VBA to open a workbook and find the first blank ro

    Try changing "Sheet1" to your sheetname.
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  6. #6
    New Member
    Join Date
    May 2004
    Posts
    27

    Default Re: Using VBA to open a workbook and find the first blank ro

    Yes, this has already been changed. But for some reason, VBA is not performing this action on the desired excel file. It is going to the first blank line of the file from which I ran the macro, not the opened file.

  7. #7
    Board Regular Cbrine's Avatar
    Join Date
    Dec 2003
    Location
    Brampton
    Posts
    3,191

    Default Re: Using VBA to open a workbook and find the first blank ro

    That's the way a macro works. It runs within the worksheet/workbook where is resides. If you want to run it on a different file, you need to reference that file. Try this.

    Dim WS as worksheet

    Set WS = workbooks("Your FileName").worksheets("Your WorkSheet")

    ws.Activate 'Change as needed
    ws.Range("A65536").End(xlUp).Offset(1,0).Select
    Xcelerated Solutions
    Office Automation Solutions for the Toronto Area

  8. #8
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default Re: Using VBA to open a workbook and find the first blank ro

    Quote Originally Posted by Cbrine
    That's the way a macro works. It runs within the worksheet/workbook where is resides {snip}
    Not really. Generally macros will run on the active workbook/worksheet. Not necessarily this workbook.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com