| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
keeping track of a range of cells in another spreadsheet or access database
Hi, Tom Ogilvy many thanks for your help on my other posting, my next
question maybe possible and I hope you or others can point me in the right direction. Having created a spreadsheet I want to output a range of cells to either another spreadsheet or access database and keep them catalogued by date order, a value held in a specific cell on the original spreadsheet. I be changing theses cells and date cell regularly and need to over write the ones held in the other spreadsheet, therefore if its a new date another entry would be added to the other spreadsheet or access database but if its a date already entered then the cells linked to that date would be over written with the new values. Does that make sense ? Obviously I will need to have a search function that looks to see if the date exists, if not enter the date and linked cell values, if it does then move to that position and overwrite the values. Hopefully someone will be able to make sense of my rantings and offer me some advice, guidance, or if possible some coding examples. Regards, Colin *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
keeping track of a range of cells in another spreadsheet or access database
Dim newDate as Date
Dim sh as Worksheet, rng as range, rng1 as Range Dim res as Variant newDate = DataSerial(2004,09,28) set Sh = workbooks("Archive.xls").Worksheets("Data") set rng = sh.Columns(1) res = Application.Match(clng(newDate), _ rng,0) if not iserror(res) then set rng1 = rng(res) ActiveCell.Resize(1,5).Copy destination:=rng1 else ActiveCell.Resize(1,5).Copy destination:=sh.Cells(rows.count,1).end(xlup)(2) End if If you want to do it with access, you would have to use ADO or something like that: http://www.erlandsendata.no/english/...php?t=envbadac -- Regards, Tom Ogilvy "Colin Evans" wrote in message ... Hi, Tom Ogilvy many thanks for your help on my other posting, my next question maybe possible and I hope you or others can point me in the right direction. Having created a spreadsheet I want to output a range of cells to either another spreadsheet or access database and keep them catalogued by date order, a value held in a specific cell on the original spreadsheet. I be changing theses cells and date cell regularly and need to over write the ones held in the other spreadsheet, therefore if its a new date another entry would be added to the other spreadsheet or access database but if its a date already entered then the cells linked to that date would be over written with the new values. Does that make sense ? Obviously I will need to have a search function that looks to see if the date exists, if not enter the date and linked cell values, if it does then move to that position and overwrite the values. Hopefully someone will be able to make sense of my rantings and offer me some advice, guidance, or if possible some coding examples. Regards, Colin *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
keeping track of a range of cells in another spreadsheet or access database
Hi, many thanks for the prompt reply and example code. Unfortunately not being knowledgeable in VBA I do find it difficult to follow the example fully, I've entered the code linked to a command button, and created a blank Archive.xls but it it doesn't seem to do anything. If I could try and explain a little more, following on from my original problem I now have cell A6 which contains the date, this is reformated to a value of YYYYMMDD which in turn is used as the filename when I click on the save button. What I want to happen is to have a range of cells notably A6, H15, I15, J15, K15, L15, M15 to be saved to the archive.xls spreadsheet, which in the first instance would be A1,B1,C1,D1,E1,F1,G1 Then as I enter new information in the original spreadsheet the date in A6 would change along with entries in H15, I15,J15 etc, if the date in A6 is a new date then another spreadsheet is saved using the reformated value, and another entry is added to the archive.xls spreadsheet, i.e A2,B2,C2,D2,E2,F2,G2 However if the date held in A6 is also present in the archive.xls spreadsheet the row where the data isheld would be found and the B,C,D,E,F,G cells would overwritten with the new or amended values. Does this make it a little clearer ? The reformatting of the date and saving of the workbook is sorted Dim sStr As String Dim year As String Dim month As String Dim day As String sStr = Worksheets("Data").Range("A6").Value day = Mid$(sStr, 1, 2) month = Mid$(sStr, 4, 2) year = Mid$(sStr, 7, 4) sStr = "p:\intranet\workbooks\info\" + year + month + day + ".xls" ActiveWorkbook.SaveAs (sStr) as is my original problem of saving the spreadsheet as a html page, with the use of another button Dim sStr As String Dim year As String Dim month As String Dim day As String sStr = Worksheets("Data").Range("A6").Value day = Mid$(sStr, 1, 2) month = Mid$(sStr, 4, 2) year = Mid$(sStr, 7, 4) sStr = year + month + day Worksheets("info").Copy ActiveWorkbook.SaveAs "P:\INTRANET\colin\info\" & sStr & ".htm", FileFormat:=xlHtml ActiveWorkbook.Close savechanges:=False I just need to master the archive.xls entries and then I'm nearly there. Hope you can make sense of the above and continue to help, many thanks once again *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
keeping track of a range of cells in another spreadsheet or access database
This assumes the sheet that contains the Date in cell A6 is named "Data" and
is located in the activeworkbook. The archive.xls file also has a sheet named "Data" where the data is written Sub Btn_click() Dim sStr As String Dim year As String Dim month As String Dim day As String Dim newDate As Date Dim sh As Worksheet, rng As Range, rng1 As Range Dim rng2 As Range Dim res As Variant sStr = Worksheets("Data").Range("A6").Value day = Mid$(sStr, 1, 2) month = Mid$(sStr, 4, 2) year = Mid$(sStr, 7, 4) sStr = "p:\intranet\workbooks\info\" + year + month + day + ".xls" 'ActiveWorkbook.SaveAs (sStr) newDate = Worksheets("Data").Range("A6").Value Set sh = Workbooks("Archive.xls").Worksheets("Data") Set rng = sh.Columns(1).Cells res = Application.Match(CLng(newDate), _ rng, 0) If Not IsError(res) Then Set rng1 = rng(res) With Worksheets("Data") .Range("A6").Copy Destination:=rng1 .Range("H15:M15").Copy Destination:=rng1(1, 2) End With Else Set rng2 = sh.Cells(Rows.Count, 1).End(xlUp)(2) If rng2.Row = 2 And IsEmpty(rng2.Offset(-1, 0)) Then _ Set rng2 = rng2.Offset(-1, 0) With Worksheets("Data") .Range("A6").Copy _ Destination:=rng2 .Range("H15:M15").Copy Destination:=rng2(1, 2) End With End If End Sub "Colin Evans" wrote in message ... Hi, many thanks for the prompt reply and example code. Unfortunately not being knowledgeable in VBA I do find it difficult to follow the example fully, I've entered the code linked to a command button, and created a blank Archive.xls but it it doesn't seem to do anything. If I could try and explain a little more, following on from my original problem I now have cell A6 which contains the date, this is reformated to a value of YYYYMMDD which in turn is used as the filename when I click on the save button. What I want to happen is to have a range of cells notably A6, H15, I15, J15, K15, L15, M15 to be saved to the archive.xls spreadsheet, which in the first instance would be A1,B1,C1,D1,E1,F1,G1 Then as I enter new information in the original spreadsheet the date in A6 would change along with entries in H15, I15,J15 etc, if the date in A6 is a new date then another spreadsheet is saved using the reformated value, and another entry is added to the archive.xls spreadsheet, i.e A2,B2,C2,D2,E2,F2,G2 However if the date held in A6 is also present in the archive.xls spreadsheet the row where the data isheld would be found and the B,C,D,E,F,G cells would overwritten with the new or amended values. Does this make it a little clearer ? The reformatting of the date and saving of the workbook is sorted Dim sStr As String Dim year As String Dim month As String Dim day As String sStr = Worksheets("Data").Range("A6").Value day = Mid$(sStr, 1, 2) month = Mid$(sStr, 4, 2) year = Mid$(sStr, 7, 4) sStr = "p:\intranet\workbooks\info\" + year + month + day + ".xls" ActiveWorkbook.SaveAs (sStr) as is my original problem of saving the spreadsheet as a html page, with the use of another button Dim sStr As String Dim year As String Dim month As String Dim day As String sStr = Worksheets("Data").Range("A6").Value day = Mid$(sStr, 1, 2) month = Mid$(sStr, 4, 2) year = Mid$(sStr, 7, 4) sStr = year + month + day Worksheets("info").Copy ActiveWorkbook.SaveAs "P:\INTRANET\colin\info\" & sStr & ".htm", FileFormat:=xlHtml ActiveWorkbook.Close savechanges:=False I just need to master the archive.xls entries and then I'm nearly there. Hope you can make sense of the above and continue to help, many thanks once again *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
keeping track of a range of cells in another spreadsheet or access database
Hi Tom, nearly there although I'm getting a few problems, maybe because I'm complicating the issue. THe cells I wish to copy and update contain values that are added up from various other cells, therefore they contain a calculation i.e =SUM(IF((C5="Colin"),1,0),IF((C11="Colin"),1,0),IF ((C15="Colin"),1,0)) when I try your solution I get #REF! entered into the cells any ideas ? on how I can get round it *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
keeping track of a range of cells in another spreadsheet or access database
Hi Tom
Well I've had a go and seem to have come up with a solution that works, albeit a bit messy Private Sub CommandButton2_Click() Dim sStr As String Dim year As String Dim month As String Dim day As String Dim newdate As String Dim sh As Worksheet, rng As Range, rng1 As Range Dim rng2 As Range Dim res As Variant sStr = Worksheets("data").Range("A6").Value day = Mid$(sStr, 1, 2) month = Mid$(sStr, 4, 2) year = Mid$(sStr, 7, 4) sStr = "p:\intranet\workbooks\info\" + year + month + day + ".xls" Worksheets("data").Range("m26").Value = year + month + day Worksheets("data").Range("m29").Value = Worksheets("data").Range("h29").Value Worksheets("data").Range("n29").Value = Worksheets("data").Range("i29").Value Worksheets("data").Range("o29").Value = Worksheets("data").Range("j29").Value Worksheets("data").Range("p29").Value = Worksheets("data").Range("k29").Value Worksheets("data").Range("q29").Value = Worksheets("data").Range("l29").Value Worksheets("data").Range("r29").Value = Worksheets("data").Range("h30").Value Worksheets("data").Range("s29").Value = Worksheets("data").Range("i30").Value Worksheets("data").Range("t29").Value = Worksheets("data").Range("j30").Value Worksheets("data").Range("u29").Value = Worksheets("data").Range("k30").Value Worksheets("data").Range("v29").Value = Worksheets("data").Range("l30").Value Worksheets("data").Range("w29").Value = Worksheets("data").Range("h31").Value Worksheets("data").Range("x29").Value = Worksheets("data").Range("i31").Value Worksheets("data").Range("y29").Value = Worksheets("data").Range("j31").Value Worksheets("data").Range("z29").Value = Worksheets("data").Range("k31").Value Worksheets("data").Range("aa29").Value = Worksheets("data").Range("l31").Value Worksheets("data").Range("ab29").Value = Worksheets("data").Range("h32").Value Worksheets("data").Range("ac29").Value = Worksheets("data").Range("i32").Value Worksheets("data").Range("ad29").Value = Worksheets("data").Range("j32").Value Worksheets("data").Range("ae29").Value = Worksheets("data").Range("k32").Value Worksheets("data").Range("af29").Value = Worksheets("data").Range("l32").Value Worksheets("data").Range("ag29").Value = Worksheets("data").Range("h33").Value Worksheets("data").Range("ah29").Value = Worksheets("data").Range("i33").Value Worksheets("data").Range("ai29").Value = Worksheets("data").Range("j33").Value Worksheets("data").Range("aj29").Value = Worksheets("data").Range("k33").Value Worksheets("data").Range("ak29").Value = Worksheets("data").Range("l33").Value ActiveWorkbook.SaveAs (sStr) newdate = Worksheets("data").Range("m26").Value Set sh = Workbooks("Archive.xls").Worksheets("data") Set rng = sh.Columns(1).Cells res = Application.Match(CLng(newdate), rng, 0) If Not IsError(res) Then Set rng1 = rng(res) With Worksheets("data") .Range("m26").Copy Destination:=rng1 .Range("M29:AK29").Copy Destination:=rng1(1, 2) End With Else Set rng2 = sh.Cells(Rows.Count, 1).End(xlUp)(2) If rng2.Row = 2 And IsEmpty(rng2.Offset(-1, 0)) Then Set rng2 = rng2.Offset(-1, 0) With Worksheets("data") .Range("m26").Copy Destination:=rng2 .Range("M29:AK29").Copy Destination:=rng2(1, 2) End With End If WHat do you think, without abusing me too much :-) The only thing I could do with doing is when I select new and the choose the template containing all the code is to make sure the archive.xls file is opened at the same time, otherwise If I forget and then try to save it comes back with the expected error, Is there a way of automatically opening the archive.xls after checking it isn't already open. Cheers for everything so far, regards, Colin *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
keeping track of a range of cells in another spreadsheet or access database
all you needed to do was
If Not IsError(res) Then Set rng1 = rng(res) With Worksheets("data") .Range("m26").Copy rng1.PasteSpecial xlValues .Range("M29:AK29").Copy rng1(1, 2).PasteSpecial xlValues End With Else Set rng2 = sh.Cells(Rows.Count, 1).End(xlUp)(2) If rng2.Row = 2 And IsEmpty(rng2.Offset(-1, 0)) Then _ Set rng2 =rng2.Offset(-1, 0) With Worksheets("data") .Range("m26").Copy rng2.PasteSpecial xlValues .Range("M29:AK29").Copy rng2(1, 2).PasteSpecial xlValues End With End If Change M26 and M29:AK29 back to the proper cell references. toward the top of your code, before you need to reference Archive.xls, put in code like: Dim wkbkA as Workbook On Error Resume Next set wkbkA = Workbooks("Archive") On Error goto 0 if wkbkA is nothing then ' Archive not open, so open it set wkbkA = Workbooks.Open("C:\MyFolder\Archive.xls") End if -- Regards, Tom Ogilvy "Colin Evans" wrote in message ... Hi Tom Well I've had a go and seem to have come up with a solution that works, albeit a bit messy Private Sub CommandButton2_Click() Dim sStr As String Dim year As String Dim month As String Dim day As String Dim newdate As String Dim sh As Worksheet, rng As Range, rng1 As Range Dim rng2 As Range Dim res As Variant sStr = Worksheets("data").Range("A6").Value day = Mid$(sStr, 1, 2) month = Mid$(sStr, 4, 2) year = Mid$(sStr, 7, 4) sStr = "p:\intranet\workbooks\info\" + year + month + day + ".xls" Worksheets("data").Range("m26").Value = year + month + day Worksheets("data").Range("m29").Value = Worksheets("data").Range("h29").Value Worksheets("data").Range("n29").Value = Worksheets("data").Range("i29").Value Worksheets("data").Range("o29").Value = Worksheets("data").Range("j29").Value Worksheets("data").Range("p29").Value = Worksheets("data").Range("k29").Value Worksheets("data").Range("q29").Value = Worksheets("data").Range("l29").Value Worksheets("data").Range("r29").Value = Worksheets("data").Range("h30").Value Worksheets("data").Range("s29").Value = Worksheets("data").Range("i30").Value Worksheets("data").Range("t29").Value = Worksheets("data").Range("j30").Value Worksheets("data").Range("u29").Value = Worksheets("data").Range("k30").Value Worksheets("data").Range("v29").Value = Worksheets("data").Range("l30").Value Worksheets("data").Range("w29").Value = Worksheets("data").Range("h31").Value Worksheets("data").Range("x29").Value = Worksheets("data").Range("i31").Value Worksheets("data").Range("y29").Value = Worksheets("data").Range("j31").Value Worksheets("data").Range("z29").Value = Worksheets("data").Range("k31").Value Worksheets("data").Range("aa29").Value = Worksheets("data").Range("l31").Value Worksheets("data").Range("ab29").Value = Worksheets("data").Range("h32").Value Worksheets("data").Range("ac29").Value = Worksheets("data").Range("i32").Value Worksheets("data").Range("ad29").Value = Worksheets("data").Range("j32").Value Worksheets("data").Range("ae29").Value = Worksheets("data").Range("k32").Value Worksheets("data").Range("af29").Value = Worksheets("data").Range("l32").Value Worksheets("data").Range("ag29").Value = Worksheets("data").Range("h33").Value Worksheets("data").Range("ah29").Value = Worksheets("data").Range("i33").Value Worksheets("data").Range("ai29").Value = Worksheets("data").Range("j33").Value Worksheets("data").Range("aj29").Value = Worksheets("data").Range("k33").Value Worksheets("data").Range("ak29").Value = Worksheets("data").Range("l33").Value ActiveWorkbook.SaveAs (sStr) newdate = Worksheets("data").Range("m26").Value Set sh = Workbooks("Archive.xls").Worksheets("data") Set rng = sh.Columns(1).Cells res = Application.Match(CLng(newdate), rng, 0) If Not IsError(res) Then Set rng1 = rng(res) With Worksheets("data") .Range("m26").Copy Destination:=rng1 .Range("M29:AK29").Copy Destination:=rng1(1, 2) End With Else Set rng2 = sh.Cells(Rows.Count, 1).End(xlUp)(2) If rng2.Row = 2 And IsEmpty(rng2.Offset(-1, 0)) Then Set rng2 = rng2.Offset(-1, 0) With Worksheets("data") .Range("m26").Copy Destination:=rng2 .Range("M29:AK29").Copy Destination:=rng2(1, 2) End With End If WHat do you think, without abusing me too much :-) The only thing I could do with doing is when I select new and the choose the template containing all the code is to make sure the archive.xls file is opened at the same time, otherwise If I forget and then try to save it comes back with the expected error, Is there a way of automatically opening the archive.xls after checking it isn't already open. Cheers for everything so far, regards, Colin *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
keeping track of a range of cells in another spreadsheet or access database
Hi Tom
Many thanks once again, problems solved and I'm happy once again, until the next problem raises its ugly head, can you recommend any books on VBA and excel, something with examples and explainations that I could follow thanks again, best regards, Colin *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
keeping track of a range of cells in another spreadsheet or access database
Look at John Walkenbach's site. He has links to descriptions of his books
http://www.j-walk.com/ss/excel -- Regards, Tom Ogilvy "Colin Evans" wrote in message ... Hi Tom Many thanks once again, problems solved and I'm happy once again, until the next problem raises its ugly head, can you recommend any books on VBA and excel, something with examples and explainations that I could follow thanks again, best regards, Colin *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I track users who access a spreadsheet? | Excel Discussion (Misc queries) | |||
| Converting Excel spreadsheet with formulae to Access database | Excel Discussion (Misc queries) | |||
| Make an Access Database out of Excel spreadsheet | New Users to Excel | |||
| Inserting an Access database into a spreadsheet? | Excel Programming | |||
| How to save an Excel range into an Access Database | Excel Programming | |||
Linear Mode
