Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving Data to xls. (2000)

    I have a spreadsheet which generates this data on a daily basis.

    (date) (number1) (number2)......(Number8)

    What i need to do is save this data in a separate spreadsheet in a particular worksheet. This spreadsheet is already created, with all the dates running sequentially down the left hand side.

    So i need to open this historical speadsheet, look up the date from the date i have selected in the current worksheet (not perhaps the actual date), and copy the 8 numbers to cells B(x) C(x) D(x) etc etc

    Anyone already have some code to do this? or any pointers on where to start ?


    update:
    Code thus far:

    <font color=blue>Private Sub CommandButton4_Click()
    Dim strFilename

    strFilename = "R:FACTORYTQMSNew P-ViewWeekly_Report.xls"

    Workbooks.Open strFilename, ReadOnly:=False




    Stop





    Workbooks(strFilename).Close</font color=blue>

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Data to xls. (2000)

    hi Steve (in Kent)

    you start with the date selected for which you want to copy data to the weekly report, then you run this macro

    Sub CopyData()
    Dim HistoricalDataSheet As Worksheet
    Dim FoundDate As Range
    Dim SelectedDate As Range
    Set SelectedDate = Selection
    Set HistoricalDataSheet = Workbooks.Open("R:FACTORYTQMSNew P-ViewWeekly_Report.xls").Worksheets(1)
    Set FoundDate = HistoricalDataSheet.Range("a:a").Find(what:=Select edDate)
    If Not FoundDate Is Nothing Then
    FoundDate.Range("b1:i1").Value = SelectedDate.Range("b1:i1").Value
    Else
    MsgBox "error - date not found in hysterical spreadsheet <img src=/S/smile.gif border=0 alt=smile width=15 height=15>"
    End If
    historicaldatasheet.parent.close false
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Data to xls. (2000)

    hi Pieter, thanks for the code..

    I added it in (frantically trying to understand it mind!) Its a fair few months of learning ahead of me i think.

    what i didn't explain , was that in the daily sheet, the Date i want to match on , is in cell B7

    And the data that i want to copy over is in cells :-

    P70 copy over to Weekly_Report, cell B (whatever date is)
    Q70 " C " "

    P71 " D

    etc etc.. for the 8 cells worth of data..

    so,,, (im guessing here) to make this work would i have to change the code as shown in red?


    <font color=blue>Dim HistoricalDataSheet As Worksheet
    Dim FoundDate As Range
    Dim SelectedDate As Range

    Set SelectedDate = <font color=red>Worksheets("Line 2").Range(B7) ' I have no idea how to refer to specific sheets.</font color=red>
    Set HistoricalDataSheet = Workbooks.Open("R:FACTORYTQMSNew P-ViewWeekly_Report.xls").Worksheets(2) <font color=magenta>' <-- changed this to 2 for second worksheet along</font color=magenta>
    Set FoundDate = HistoricalDataSheet.Range("a:a").Find(what:=Select edDate)

    If Not FoundDate Is Nothing Then
    <font color=red>' FoundDate.Range("b1:i1").Value = SelectedDate.Range("b1:i1").Value
    Copy over each cell individually here ?</font color=red> Else
    MsgBox "error - date not found in hysterical spreadsheet "
    End If
    HistoricalDataSheet.Parent.Close False</font color=blue>

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Data to xls. (2000)

    I went back a step, and tried to see if it would find the date, and it won't do a match. I've checked that both the B7 cell in the Source workbook and the Column A in the destination workbook are formatted the same (as a short date, 20/06/2003) ..so the code is now :-

    <font color=blue>Dim HistoricalDataSheet As Worksheet
    Dim FoundDate As Range
    Dim SelectedDate As Range

    Set SelectedDate = Worksheets("Line 2").Range("B7")
    Set HistoricalDataSheet = Workbooks.Open("R:FACTORYTQMSNew P-ViewWeekly_Report.xls").Worksheets(2)
    Set FoundDate = HistoricalDataSheet.Range("a:a").Find(what:=Select edDate)


    If Not FoundDate Is Nothing Then
    'FoundDate.Range("b1:i1").Value = SelectedDate.Range("b1:i1").Value ' Here we copy over the data


    MsgBox "Found the date"

    Else
    MsgBox "Error - date not found in spreadsheet "
    End If


    HistoricalDataSheet.Parent.Close False

    End Sub</font color=blue>

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Data to xls. (2000)

    hi Steve,

    Set SelectedDate = Worksheets("Line 2").Range(B7)

    this will work if the sheet is called Line 2 and is on the activeworkbook, otherwise you need to fully qualify the path to the cell:
    Set SelectedDate = workbooks("mydateworkbook.xls").Worksheets("Line 2").Range(B7)
    but i think you're modification will work fine.
    the layout of the source and data cells is still not very clear to me, i only know you want to copy 8 values. if they are not in a single range, then you can use the offset property to individually copy them
    the find statement remains the same, but then you use
    founddate.offset(0,1).value=selecteddate.offset(1, 1)
    founddate.offset(0,2).value=selecteddate.offset(2, 2)

    or if the eight cells are always in the same range you can use
    with workbooks("mydateworkbook.xls").Worksheets("Line 2")
    founddate.offset(0,1).value=.range("Q70")
    founddate.offset(0,2).value=.range("P71")
    '...other statements to copy the remaining cells
    end with

    also you need the modify the last statement, because this will cause the weekly report sheet to be closed without saving. simply remove the false to amend this:
    HistoricalDataSheet.Parent.Close

    you will be prompted to save the workbook.

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Data to xls. (2000)

    Pieter, thanks for the further suggestion, im still trying to understand the code. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    At the moment, as the code above shows, i took out the actual copying over bit, and am just trying to get it to match the B7 date , to a row in the Weekly workbook. but at the moment, it refuses to compare the two dates, ie: i never see the first messagebox that i put in.

    I will try to explain further :

    Cell B7 in the source workbook contains a short date (25/06/2003)

    Column A in the target workbook contains all the dates from 25/06/2003 to 2005 (short date format)

    When the code fires, i want it to match the correct row from B7 to the correct row in the target workbook, and then copy over 8 cells in the source workbook (that contain numbers) into cells B,C,D ..I, of the target workbook, save the target workbook, then close it.

    The source 8 cells with numbers in are not together.

    so at the moment, its not matching the date in the target workbook with cell B7.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Data to xls. (2000)

    PMFJI.

    If you step through the code, what value is shown for the date you are looking for in the Find method?

    Also: if you do not fully qualify the options of the Find method in your code, Excel will use the options last used (which can be a manual find from the UI too!!!). The options may be different each time your macro is run, revealing unexpected results.!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Data to xls. (2000)

    Some further testing revealed this:

    In cell B7 i have a date, 20/06/03. The cell format is set to short date. This date is put there via VBA, [basically setting it in code to NOW() -1] if you select it, up the top in the formulae bar it will display <font color=red>20/06/03 09:16</font color=red>

    So its displaying the time. and it wont match with the target spreadsheet.

    now,,, i tried making c6 point to c7 with (=c7) and set the date format to short date, click on the cell and it only shows <font color=red>20/06/03 </font color=red> (with no time) but again the code wont find a match...


    ok.. so now i deleted that and TYPED in 20/06/03.... and it found a MATCH!!! <img src=/S/aflame.gif border=0 alt=aflame width=16 height=16>

    So to me, this tells me that there is hidden information somewhere, as to the nature of a cell, and that setting it to short date doesnt matter a damn...

    so atm im flumoxed...


    Also, i need it to auto save the target workbook and close it. as you mentioned pieter, it asks to save. I tried adding in

    <font color=ff6666>HistoricalDataSheet.SaveAs "R:FACTORYTQMSNew P-ViewWeekly_Report.xls</font color=ff6666>
    but that asks if you want to overwrite the old one, and i can't see an auto 'yes' option in the saveas help.

  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: Saving Data to xls. (2000)

    The displayed date has NOTHING to do with the value in the cell. The value is a "serial date" the integer portion is the number of days past jan 1, 1900. The "decimal portion" is fractions of a day (0.5 = 12 hours = Noon).

    You can check the search date value vs the integer of the entered date by converting with INT function to remove the "time"/decimal portion.

    Or you can use DATE in VB instead of NOW to put just the DATE portion if you do NOT need the time portion saved.

    Steve

  10. #10
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Data to xls. (2000)

    Thanks for the tip Steve, as you suggested, i made the cell point to the date using =int(C7) and that made it work correctly.

    So the bit im stuck on now, is how to make it save the target spreadsheet, without prompting for anything.

    I'm slowly working my way thru hundreds of posts looking for the code that makes it happen! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

  11. #11
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Data to xls. (2000)

    Ahaa no sooner do i write the text than i find the code on about page 30

    HistoricalDataSheet.Parent.Close SaveChanges:=True

    seems to work <img src=/S/cool.gif border=0 alt=cool width=15 height=15>


    Not exactly sure whether the Parent text is needed, or what it does... but ho hum



    Thanks for the help Pieter, Steve <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  12. #12
    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: Saving Data to xls. (2000)

    To close and saveit:
    Activeworkbook.Close SaveChanges:=True

    Activeworkbook.save

    will save it

    activeworkbook.SaveAs Filename:=fName

    will save it with a different name.

    What prompts are you worried about?

    Steve

  13. #13
    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: Saving Data to xls. (2000)

    Historicaldatasheet is a worksheet, you save workbook objects. The PARENT to a worksheet is the workbook.

    Steve

  14. #14
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Data to xls. (2000)

    glad you got it working.

    since the HistoricalDataSheet object variable is a reference to a sheet, we need the parent property of the sheet to get to the workbook (iow the workbook is the parent of the sheet, the sheet is a child of the workbook in object oriented speak). So excel conveniently provides the parent property to go one level up in the object hierarchy to get to the workbook, and then we apply the close method with the parameter savechanges set to true to assure the workbook is properly saved. for most vba keywords (put your cursor in it and press F1) , online help contains explanations which are fairly good.

Posting Permissions

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