Results 1 to 11 of 11
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code only runs once (2000)

    You have some references to ranges that are not explicitly associated with xlSht. For example, in

    .Range(Cells(1, 5), Cells(1, intCount + 4)).Select

    Cells should be .Cells (twice). Similarly, later on,

    Columns("A").Select

    and

    Range("A1").Select

    should have .Columns and .Range. By the way, the lines

    .Columns("A").Select
    Selection.AutoFit

    can be replaced by

    .Columns("A").AutoFit

  2. #2
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code only runs once (2000)

    I have some code I use in Access after I export a query to an Excel file.
    It does some formatting and only works once. If I try to do the process again it hangs
    at the .Range statement.
    I tried two different types of statements but the same thing happens.
    Excel doesn't seem to close out of memory after the code runs. Even stopping it from Ctl-Alt-Del and trying again.
    The only time it seems to work again is the third time.
    I'm not too good in Excel syntax so I'm sure I'm doing something wrong.

    Scott

    ...Code that does the export happens here first......

    <pre> Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlSht As Excel.Worksheet

    Set xlApp = CreateObject("excel.Application")
    Set xlWbk = xlApp.Workbooks.Open(strFILE)
    Set xlSht = xlWbk.Worksheets(1)

    xlApp.Visible = False

    With xlSht.Cells.Font
    .NAME = "Arial"
    .Size = 10
    End With
    With xlSht
    .Rows("1:1").RowHeight = 165
    '.Range("E1:AZ1").Select
    .Range(Cells(1, 5), Cells(1, intCount + 4)).Select
    'intCount is a count of the number of columns used.
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = True
    .Orientation = 90
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Columns.AutoFit
    Columns("A").Select
    Selection.Columns.AutoFit

    Range("A1").Select
    End With

    xlWbk.Close SaveChanges:=True
    xlApp.Quit
    Set xlSht = Nothing
    Set xlWbk = Nothing
    Set xlApp = Nothing</pre>


  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code only runs once (2000)

    Hans,
    Thanks that did it. It also fixed the problem of Excel releasing from memory.
    One other thing
    How would I find the last cell used in a range in this statement.
    .Range(.Cells(2, 5), .Cells(last cell used)) like hitting Ctrl+End

    Thanks as always,
    Scott

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code only runs once (2000)

    If you have a contiguous range, you can use

    .Range(.Cells(2, 5), .Cells(2, 5).End(xlDown))
    or
    .Range(.Cells(2, 5), .Cells(2, 5).End(xlToRight))

    depending on whether you want to extend down or to the right. If there can be gaps, and you want to extend to the last non-blank cell, you can use

    .Range(.Cells(2, 5), .Cells(65536, 5).End(xlUp))
    or
    .Range(.Cells(2, 5), .Cells(2, 256).End(xlToLeft))

    again depending on whether you want to extend down or to the right.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Code only runs once (2000)

    Try:
    .range(.range("e2"), .range("E2").SpecialCells(xlCellTypeLastCell)).Sel ect

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code only runs once (2000)

    Huge screenshot reduced in size by HansV - please don't exceed 640 x 480.

    Hans,
    I probably didn't explain what I wanted to do correctly so I attached a picture of what I'm trying to do.
    Your .Range suggestion just select a row or a column. I wanted to select the whole grid area from Cell E2 to the
    last cell on the bottom right.
    Thanks,
    Scott

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code only runs once (2000)

    Or use .Range("E2").CurrentRegion

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Code only runs once (2000)

    I was worried about "current region" with all the blanks.

    It should be the same even with the blank columns due to the header row, but I wasn't sure if some of the rows might be completely blank and if so, current region would not choose all the dataset.

    Steve

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code only runs once (2000)

    Good point.

  10. #10
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code only runs once (2000)

    Hans,
    I used the previous range you gave me and it works.
    One more question and I'll stop bugging you......today <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    How do I put all borders around that range. I tried creating a macro to see if that code would work but it didn't like the .borders line.
    .Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic

    Thanks,
    Scott

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code only runs once (2000)

    You need something like

    With .Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With

    or, to set all four borders at the same time:

    With .Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With

Posting Permissions

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