Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I getting data from another worksheet?
I am trying to obtain the filename from the user than I store that filename
in the range "ReadFromFilename". Then I am trying to use this filename from that range in determining the filename to read from. The user click on a button which opens a file open dialog box, I obtain the name from that. This filename I then put into the range ReadFromFilename. I then use this name to determine which file the user wants me to obtain values from. I also obtain the WriteToFilename so that I can grab data from a newer version of my program. I am trying to make it easier for users of my program to migrate from an earlier version to a newer version of my program. Dave Marden Here is some code I've seen but for what I'm talking 'bout what I am showing above. Any help would be appreciated. Assuming that the workbooks are open: Private Sub cmdRead_Click() Workbooks("Data Gatherer For Scoreboard.xls").Worksheets("Sheet1").Range("Handi cap") = Workbooks("ReadFromFilename.xls").Worksheets("Comp etitors A-Z").Range("D29") End Sub Private Sub cmdWrite_Click() Workbooks("WriteToFilename.xls").Worksheets("Compe titors A-Z").Range("D29") = Workbooks("Data Gatherer For Scoreboard.xls").Worksheets("Sheet1").Range("Handi cap") End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I getting data from another worksheet?
Private Sub cmdRead_Click()
Dim s as string, bk as workbook Dim bClosed as Boolean s = Thisworkbook.Name("ReadFromFilename").RefersToRang e.Value On error Resume Next set bk = Workbooks(s & ".xls") On error goto 0 if bk is nothing then bClosed = True set bk = Workbooks.open("C:\MyFolder\" & s & ".xls") End if Workbooks("Data Gatherer For Scoreboard.xls") _ .Worksheets("Sheet1").Range("Handicap") = _ Bk.Worksheets("Competitors A-Z").Range("D29") if bClosed then bk.Close SaveChanges:=False End Sub You can also refer to it as s = Range("ReadFromFilename").Value Private Sub cmdWrite_Click() Dim s as string, bk as workbook Dim bClosed as Boolean s = Thisworkbook.Name("WriteToFilename").RefersToRange .Value On error Resume Next set bk = Workbooks(s & ".xls") On error goto 0 if bk is nothing then bClosed = True set bk = Workbooks.open("C:\MyFolder\" & s & ".xls") End if bk.Worksheets("Competitors A-Z").Range("D29") = _ Workbooks("Data Gatherer For Scoreboard.xls") _ .Worksheets("Sheet1").Range("Handicap") if bClosed then bk.Close SaveChanges:=True End Sub -- Regards, Tom Ogilvy "Dave Marden" wrote in message ... I am trying to obtain the filename from the user than I store that filename in the range "ReadFromFilename". Then I am trying to use this filename from that range in determining the filename to read from. The user click on a button which opens a file open dialog box, I obtain the name from that. This filename I then put into the range ReadFromFilename. I then use this name to determine which file the user wants me to obtain values from. I also obtain the WriteToFilename so that I can grab data from a newer version of my program. I am trying to make it easier for users of my program to migrate from an earlier version to a newer version of my program. Dave Marden Here is some code I've seen but for what I'm talking 'bout what I am showing above. Any help would be appreciated. Assuming that the workbooks are open: Private Sub cmdRead_Click() Workbooks("Data Gatherer For Scoreboard.xls").Worksheets("Sheet1").Range("Handi cap") = Workbooks("ReadFromFilename.xls").Worksheets("Comp etitors A-Z").Range("D29") End Sub Private Sub cmdWrite_Click() Workbooks("WriteToFilename.xls").Worksheets("Compe titors A-Z").Range("D29") = Workbooks("Data Gatherer For Scoreboard.xls").Worksheets("Sheet1").Range("Handi cap") End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I getting data from another worksheet?
Thanks Tom for the assistance, I have gotten that to work but if you
look at my next problem I would appreciate it. My program is all protected and many data ranges are hidden such as the actual position where data is entered via a userform. I need to get ranges such as X4:X27 into the new version of the program. For some reason this doesn't seem to be working for me. Here is what I have so far, any help would be appreciated. I can't imagine having to do each cell individually, but when I select multiple cell ranges the program seems to just ignore the request as in no error and no copy. The line with 'Names is my problem area now. Thanks In Advance, Dave Marden Private Sub cmdRead_Click() Dim wbName As String, bk As Workbook Dim bClosed As Boolean wbName = Range("ReadFromFilename").Value On Error Resume Next Set bk = Workbooks(wbName) On Error GoTo 0 If bk Is Nothing Then bClosed = True Set bk = Workbooks.Open(wbName) End If 'Getting Data From Old Version 'Handicap Workbooks("Data Gatherer For Scoreboard.xls").Worksheets("Sheet1").Range("Handi cap") = bk.Worksheets("Competitors A-Z").Range("D29") 'Archery League Name Workbooks("Data Gatherer For Scoreboard.xls").Worksheets("Sheet1").Range("Arche ryLeagueName") = bk.Worksheets("League's Score Board").Range("ArcheryLeagueName") 'Max Make-Up Scores Workbooks("Data Gatherer For Scoreboard.xls").Worksheets("Sheet1").Range("MaxMa keupScores") = bk.Worksheets("Competitors A-Z").Range("MaxMakeupScores") 'Names Workbooks("Data Gatherer For Scoreboard.xls").Worksheets("Sheet1").Range("B2:B2 5") = bk.Worksheets("Competitors A-Z").Range("X4:X27") If bClosed Then bk.Close Savechanges:=False End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I getting data from another worksheet?
I have figured out the last part of that problem, however, I have decided to
incorporate this change into all upcoming versions of my program. This has brought up a new problem. I see that you use "Set bk = Workbooks(wbName)" in referencing the filename so I tried to use the same idea but for some reason it always gives me a "runtime error 9" "Subscript out of range" error. I simply used bk2 for doing this. I figured I didn't need the error checking since this file is obviously going to be already running. Public Sub cmdPullDataFromOldFile_Click() Dim wbName As String, bk As Workbook Dim bk2 As Workbook wbName = Application.GetOpenFilename wbName2 = ActiveWorkbook.FullName set bk2 = Workbooks(wbName2) Dim Msg, Style, Title, Help, Ctxt, Response, MyString Msg = "Are you sure you want to copy all user input data from " & wbName & " to this file?" ' Define message. Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons. Title = "Confirm Data Update" ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) If Response = vbYes Then ' User chose Yes. Dim bClosed As Boolean On Error Resume Next Set bk = Workbooks(wbName) On Error GoTo 0 If bk Is Nothing Then bClosed = True Set bk = Workbooks.Open(wbName) End If 'Getting Data From Old Version 'Handicap bk2.Worksheets("Competitors A-Z").Range("D29") = bk.Worksheets("Competitors A-Z").Range("D29") 'Archery League Name bk2.Worksheets("League's Score Board").Range("ArcheryLeagueName") = bk.Worksheets("League's Score Board").Range("ArcheryLeagueName") 'Max Make-Up Scores bk2.Worksheets("Competitors A-Z").Range("MaxMakeupScores") = bk.Worksheets("Competitors A-Z").Range("MaxMakeupScores") 'Names bk2.Worksheets("Competitors A-Z").Range("X4:X27").Value = bk.Worksheets("Competitors A-Z").Range("X4:X27").Value 'Scores, X-Counts, Make-Up, and Blind Data bk2.Worksheets("Competitors A-Z").Range("AB4:BK27").Value = bk.Worksheets("Competitors A-Z").Range("AB4:BK27").Value If bClosed Then bk.Close Savechanges:=False Else ' User chose No. MsgBox "You Have Chosen Not To Update This File With Another Files Data" End If End Sub Any help appreciated, Dave Marden |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically appending newly added data on worksheet to a master list worksheet | Links and Linking in Excel | |||
Pulling data into today's worksheet from yesterday's worksheet | Excel Discussion (Misc queries) | |||
Using a Worksheet Form to add data to a separate worksheet databas | Excel Worksheet Functions | |||
Using a column of data from 1 worksheet to extract data from another worksheet | Excel Worksheet Functions | |||
Attaching a JET database to an Excel Worksheet OR storing large binary data in a worksheet | Excel Programming |