Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update FileName? (Excel 2000)

    Afternoon all.....I know I keep saying "last question", but really I think this time it is.

    Below is code that runs when user selects a command button from a form. All is well...well kinda.....I want "O2" and beyond (i.e. O3, O4, etc) to be updated accordingly each time the command button is selected.

    Private Sub cmdAsthma_Click()
    Dim wkbXLTemp As Workbook, wkbXLSheet As Workbook
    Dim wshSource As Worksheet, wshTarget As Worksheet
    Dim rngSource As Range, rngTarget As Range
    Dim strFileName As String, strFNRange As Range

    Set wkbXLTemp = ActiveWorkbook
    Set wkbXLSheet = Workbooks.Open("T:All_NHSOperatorDM Monthly Reports.xls")

    'Set wshSource = Worksheets("Asthma")
    Set wshSource = Workbooks("City of Two Rivers1").Worksheets("Asthma")

    strFileName = Trim(Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1))
    Set strFNRange = wshSource.Range("A65536").End(xlUp)*********I'm certain this is the line that needs to be fixed....it should ONLY look at Column "O" and beyond
    strFNRange = strFileName

    Set wshTarget = Workbooks("DM Monthly Reports").Worksheets("EAsthma")
    Set rngSource = wshSource.Range("A65536").End(xlUp).Resize(1, 15)

    Set rngTarget = wshTarget.Range("A65536").End(xlUp).Offset(1, 0).Resize(1, 15)

    rngSource.Copy
    rngTarget.PasteSpecial Paste:=xlPasteValues

    wkbXLSheet.Save
    wkbXLSheet.Close

    Set rngTarget = Nothing
    Set rngSource = Nothing
    Set wshTarget = Nothing
    Set wshSource = Nothing

    ufExport.Show
    Sheets("Export").Visible = True
    Sheets("Export").Select
    Sheets("Asthma").Visible = False
    End Sub

    Probably something simple...but I've spent the last 3 hrs trying to figure it out....have most of it...

    Thanks

    ps.....the entire code works .... except I need only "O" updated.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Update FileName? (Excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Roberta

    OK I looked very brefly at your code, I do suffer from Asthma myself, so I was interested in what you have.

    I would suggest:

    Chnage the line:

    Set strFNRange = wshSource.Range("A65536").End(xlUp) to

    Set strFNRange = wshSource.Range("O65536").End(xlUp).

    If Column O is not populated check the Offset function, and you can offset the range you want to work with from the range you are setting.

    One other thing you can do, or simply check the number of rows you need to work within and set your range as OFirst_Row:OLast_Row.

    Hope this helps.

    Wheezing and Wheezing... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update FileName? (Excel 2000)

    Thanks for the suggestion.

    It however was one I had already tried .... with and without the offset. Still no luck....."O" isn't populated with the file name as is should be.

    Again Thanks!
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    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: Update FileName? (Excel 2000)

    <P ID="edit" class=small>(Edited by sdckapr on 21-Jun-04 13:49. Added PS)</P>Not sure exactly what you are after. Are you looking for:

    with wshSource
    strFNRange = .range(.range("o2"), .Range("o65536").End(xlUp)).address
    end with

    This takes the address from o2 to the last occupied cell in col O and assigns it to the string variable strFNRange (note there is no SET for a string)

    Or are you looking for assigning it to a range object?

    Dim rFNRange as range
    with wshSource
    Set rFNRange = .range(.range("o2"), .Range("o65536").End(xlUp))
    end with

    This takes the address from o2 to the last occupied cell in col O and assigns it to the range object rFNRange

    Your line:
    Set strFNRange = wshSource.Range("A65536").End(xlUp)
    tries to place in a string the last occupied cell from column A. You can not do this. Use set for an object, just = for a string. If you want a string you must choose a property of the cell (a range object) that results in a string.

    Steve
    PS or perhaps are you looking for the value in the last cell of col O?
    strFNRange = wshSource.Range("O65536").End(xlUp)

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update FileName? (Excel 2000)

    Sorry, I can't figure out what you want this code to do vs what it is doing. Here are some things that I see:

    First, in these two lines of code:

    <pre>Set strFNRange = wshSource.Range("A65536").End(xlUp)*********I'm certain this is the line that needs to be fixed....it should ONLY look at Column "O" and beyond
    strFNRange = strFileName
    </pre>


    1- You have a variable named strFNRange which is DIMed as a range object, but the name indicates that it is a string variable. You then first use this variable as a range object, but the second line then assigns a string to the variable. This does not make any sense. If this is not giving you an error, it is because Excel is creating two different variables, one an object and the other a variant.

    2- The line you flag most likely can not be your problem because I can't find where strFNRange is ever used again to do anything. Therefore, whatever it contains can't be affecting anything else.


    Second, you indicate that you want to operate down column O. However, you have code like this:

    <pre>Set rngSource = wshSource.Range("A65536").End(xlUp).Resize(1, 15)
    </pre>


    That code finds the last used cell in column A and then resizes that cell to be a range that is one row high by 15 columns wide.

    What are you trying to do? Copy the range starting at O2 down to the last used cell in Column O? Copy the first 15 cells down column O starting at O2? Copy the last 15 cells in column O?
    Legare Coleman

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update FileName? (Excel 2000)

    Legare,

    Thanks for your help.

    1. It should read strFNRange as Range ....(I must have booboo'd or something when I pasted here...it doesn't appear that way in the code)

    2. Line 2, I still think this is where my problem is....I'm sure I haven't done something correctly to make SOMETHING HAPPEN here....for instance ...(in my simple mind..that usually doesn't work so well), the line above Set rngFNRange = wshSource, assigns the FileName to strFileName, the line below it, I thought, assigned the value of strFileName the rngFNRange ....better said:

    I thought what I was doing is 1, assigning a value (the name of the FILE) to strFileName, 2) setting the Range for rngFNRange (Column O), and 3) assigning strFileName to rngFNRange

    3. The line you refer to --------------Set rngSource = wshSource.Range("A65536").End(xlUp).Resize(1, 15) is for a copy and paste part of the code....which is working correctly...and yes I want all 15. This line has nothing to do with the above issue, unless placement is an issue here. So the answer to your question here is yes ... exactly what I'm wanting to do and it is...no problem....but this peice of the code is different than that above. All I want to do with the above is place the name of the file in Column O, Row ???? (the last one that is empty) and then move through the remainder of the code.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    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: Update FileName? (Excel 2000)

    If you only want to place the filename in the next available one (the one past the last filled if this is what you meant by "the last one that is empty") then use:
    <pre>wshSource.Range("o65536").End(xlUp).offset(1, 0).value = strFileName</pre>


    Steve

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update FileName? (Excel 2000)

    Thank you......exactly what I wanted.....

    Have a great evening!!
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    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: Update FileName? (Excel 2000)

    You are welcome.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Then you don't even need the strFNRange variable at all, so you can remove the DIM.

    Steve

Posting Permissions

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