Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
automatically appending newly added data on worksheet to a master list worksheet tabylee via OfficeKB.com Links and Linking in Excel 0 December 17th 09 05:24 PM
Pulling data into today's worksheet from yesterday's worksheet Big UT Fan Excel Discussion (Misc queries) 4 December 12th 08 08:20 PM
Using a Worksheet Form to add data to a separate worksheet databas Rawblyn Excel Worksheet Functions 3 March 7th 06 09:17 PM
Using a column of data from 1 worksheet to extract data from another worksheet [email protected] Excel Worksheet Functions 2 February 23rd 06 05:33 PM
Attaching a JET database to an Excel Worksheet OR storing large binary data in a worksheet Ant Waters Excel Programming 1 September 3rd 03 11:34 AM


All times are GMT +1. The time now is 06:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"