[Solved] VBA: Referencing another spreadsheet

rbjexcpa · Aug 6th 2003

Can I put in a worksheet address for a range that is in another spreadsheet? I.e. in the example below - if the range IDList was in another spreadsheet?

Also, How do I reference that spreadsheet for where the data will be stored (CurrentlySheets(DATABASE_SHEET) in this Macro)

Sub returnevaluated()
'WillR.... http://www.ozgrid.com/forum
Dim rowindex As Long ' always dim your variables
Dim irow As String
Dim myrange As Range

TEMPLATE_SHEET = "ScoringForm"
DATABASE_SHEET = "DataStorage"

irow = Range("CASEID").Value 'i.e. the case id
Set myrange = Range("IDList") 'the lookuplist
rowindex = Application.Match(irow, myrange, 0) + 1
'gets the rowno of the case id in database...
'the rest is easy
Sheets(DATABASE_SHEET).Range("L" & rowindex).Value = Range("SCORE").Value
Sheets(DATABASE_SHEET).Range("M" & rowindex).Value = Range("COM1").Value
Sheets(DATABASE_SHEET).Range("N" & rowindex).Value = Range("COM2").Value
Sheets(DATABASE_SHEET).Range("O" & rowindex).Value = Range("COM3").Value
Sheets(DATABASE_SHEET).Range("P" & rowindex).Value = Range("COM4").Value

Application.Goto Reference:="COM1"
Selection.ClearContents
Application.Goto Reference:="COM2"
Selection.ClearContents
Application.Goto Reference:="COM3"
Selection.ClearContents
Application.Goto Reference:="COM4"
Selection.ClearContents

End Sub

Attached is the spreadsheet that works perfectly as long as ScoringForm is a sheet within DataCentral. However, I would like to have ScoringForm be a separate spreadsheet called ScoringForm.xls and maintain the same functionality.

Thanks!

Rod

Egad · Aug 6th 2003

Hi Rod, it should work if you specify the other workbook. eg:
Workbooks("book2.xls").Sheets("sheet1").range("score").value

Also, you don't have to goto or select the range that you want to clear. Just identify it without going to it. eg:
Workbooks("book2.xls").Sheets(TEMPLATE_SHEET).Range("com1").ClearContents

rbjexcpa · Aug 6th 2003

Thanks for the help. It is showing out of range if I use the following changes. Let me know where I messed up.

Thanks!

Rod

Sub returnevaluated()
'WillR.... http://www.ozgrid.com/forum
Dim rowindex As Long ' always dim your variables
Dim irow As String
Dim myrange As Range

TEMPLATE_SHEET = "ScoringForm"
DATABASE_SHEET = "DataStorage"
Workbook = "C:\Documents and Settings\rjex\Desktop\Data Centralv1.xls"

irow = Range("CASEID").Value 'i.e. the case id
Set myrange = Workbooks(Workbook).Sheets(DATABASE_SHEET).Range("IDList") 'the lookuplist
rowindex = Application.Match(irow, myrange, 0) + 1

'gets the rowno of the case id in database...
'the rest is easy
Workbooks(Workbook).Sheets(DATABASE_SHEET).Range("L" & rowindex).Value = Range("SCORE").Value
Workbooks(Workbook).Sheets(DATABASE_SHEET).Range("M" & rowindex).Value = Range("COM1").Value
Workbooks(Workbook).Sheets(DATABASE_SHEET).Range("N" & rowindex).Value = Range("COM2").Value
Workbooks(Workbook).Sheets(DATABASE_SHEET).Range("O" & rowindex).Value = Range("COM3").Value
Workbooks(Workbook).Sheets(DATABASE_SHEET).Range("P" & rowindex).Value = Range("COM4").Value

Sheets(TEMPLATE_SHEET).Range("COM1").ClearContents
Sheets(TEMPLATE_SHEET).Range("COM2").ClearContents
Sheets(TEMPLATE_SHEET).Range("COM3").ClearContents
Sheets(TEMPLATE_SHEET).Range("COM4").ClearContents

End Sub

Egad · Aug 6th 2003

Hi Rod, you don't need the full path of the workbook - just the name. (make sure its open though - or did you want it closed?)

Workbook = "Data Centralv1.xls"

PS: Although it works, I am hesitant to call a variable 'workbook' as it is also a VBA object name -it may cause conflicts - how about 'workbookx'?

rbjexcpa · Aug 6th 2003

Yes, I need it to be closed, as DataCentral is an admin spreadsheet that the user of ScoringForm will never have access to. However, the data submitted by ScoringForm needs to go to DataCentral.

(I would prefer it if the DataCentral workbook is not opened, but rather, that the macro knows where to go to update the data)

Thanks!

Rod

Egad · Aug 6th 2003

You can grab cells from a closed file like this:

Sub ClosedGrabber()
ThisPath = "c:\documents and settings\what\"
ThisFile = "book2.xls"
ThisSheet = "sheet1"
ThisCell = "testrange"

ThisFile = "'" & ThisPath & "[" & ThisFile & "]" & ThisSheet & "'!" & ThisCell
Range("a1").Value = ExecuteExcel4Macro(ThisFile)
End Sub

rbjexcpa · Aug 6th 2003

Is grabbing cells the same as submitting data to cells?

What I am trying to do is use a submit form to send data to another worksheet that is closed and just updates the cells based on the ranges given.

Right now I have one stumbling block I am unable to get past as contained in the VBA Macro listed below:

Set myrange = Workbooks(Workbook).Sheets(DATABASE_SHEET).Range("IDList") 'the lookuplist

I suspect the problem with this statement is the Range that exists in the DataCentral workbook but doesn't pull up in this manner. (I have changed (Workbook) to (Workbookx) also.

Thanks,

Rod

Egad · Aug 6th 2003

Oh, I had it backwards.
I don't believe you can make changes to a workbook (change cell values) without opening, changing then saving it.

rbjexcpa · Aug 6th 2003

How about opening the workbook, updating the cells, then closing it?

Egad · Aug 6th 2003

Yup, like this:

VarThisFileName = ActiveWorkbook.Name
VarOtherPath = "y:\whatever\"
VarOtherFileName = "hello.xls"
VarOtherFullFileName = VarOtherPath & VarOtherFileName

Workbooks.Open Filename:=VarOtherFullFileName
'do stuff
Windows(VarOtherFileName).Close SaveChanges:=True
Windows(VarThisFileName).Activate

rbjexcpa · Aug 6th 2003

Oh, this is so close!

Here is the code. Everything seems to be working except for one crucial detail. It is not copying the information from ScoringForm Range."Com1" to the other workbook.

So while I have the other workbook (DataCentralv1) open I need it to copy the information from ScoringForm. So what do I need to do to change the following statements?

Sheets(DATABASE_SHEET).Range("L" & rowindex).Value = Range("SCORE").Value
Sheets(DATABASE_SHEET).Range("M" & rowindex).Value = Range("COM1").Value
Sheets(DATABASE_SHEET).Range("N" & rowindex).Value = Range("COM2").Value
Sheets(DATABASE_SHEET).Range("O" & rowindex).Value = Range("COM3").Value
Sheets(DATABASE_SHEET).Range("P" & rowindex).Value = Range("COM4").Value

Also, the reason I need the goto references is because those fields are merged fields.

Thanks,

Rod

Sub returnevaluated()
'WillR.... http://www.ozgrid.com/forum
Dim rowindex As Long ' always dim your variables
Dim irow As String
Dim myrange As Range

TEMPLATE_SHEET = "ScoringForm"
DATABASE_SHEET = "DataStorage"

irow = Range("CASEID").Value 'i.e. the case id

VarThisFileName = ActiveWorkbook.Name
VarOtherPath = "C:\Documents and Settings\rjex\Desktop\"
VarOtherFileName = "Data Centralv1.xls"
VarOtherFullFileName = VarOtherPath & VarOtherFileName

Workbooks.Open Filename:=VarOtherFullFileName
Set myrange = Sheets(DATABASE_SHEET).Range("IDList") 'the lookuplist
rowindex = Application.Match(irow, myrange, 0) + 1
'gets the rowno of the case id in database...

'the rest is easy

Sheets(DATABASE_SHEET).Range("L" & rowindex).Value = Range("SCORE").Value
Sheets(DATABASE_SHEET).Range("M" & rowindex).Value = Range("COM1").Value
Sheets(DATABASE_SHEET).Range("N" & rowindex).Value = Range("COM2").Value
Sheets(DATABASE_SHEET).Range("O" & rowindex).Value = Range("COM3").Value
Sheets(DATABASE_SHEET).Range("P" & rowindex).Value = Range("COM4").Value

Windows(VarOtherFileName).Close SaveChanges:=True
Windows(VarThisFileName).Activate

Application.Goto Reference:="COM1"
Selection.ClearContents
Application.Goto Reference:="COM2"
Selection.ClearContents
Application.Goto Reference:="COM3"
Selection.ClearContents
Application.Goto Reference:="COM4"
Selection.ClearContents

'Sheets(TEMPLATE_SHEET).Range("COM1").ClearContents
'Sheets(TEMPLATE_SHEET).Range("COM2").ClearContents
'Sheets(TEMPLATE_SHEET).Range("COM3").ClearContents
'Sheets(TEMPLATE_SHEET).Range("COM4").ClearContents

End Sub

rbjexcpa · Aug 7th 2003

Been wracking my brain on other alternatives to make this spreadsheet work, but it would be much easier if I used VBA since I already have everything else working.

Thanks!

Anonymous · Aug 7th 2003

I'm coming into this late and may not understand what is in what workbook, but it appears that the problem with a statement like
Sheets(DATABASE_SHEET).Range("L" & rowindex).Value = Range("SCORE").Value

is that Excel is expecting Range("Score") to be in the active workbook. But I think it is actually in varthisfilename. If that is the case you will need to qualify the range. If I misundersatnd the layout, ignore my remark.

rbjexcpa · Aug 7th 2003

Yes that is correct. The range for the lookup list is set for VarOtherFileName. Now I just need to transfer the data from VarThisFileName over to the cells identified by the lookup.

I'm sure this is simple to do, I hope...

Thanks!

Anonymous · Aug 7th 2003

Try this.

Code

Sub returnevaluated()
'WillR.... [url]www.ozgrid.com/forum[/url]
    Dim rowindex As Long    ' always dim your variables
    Dim irow As String
    Dim myrange As Range


    TEMPLATE_SHEET = "ScoringForm"
    DATABASE_SHEET = "DataStorage"


    irow = Range("CASEID").Value    'i.e. the case id


    Set mysheet = Sheets(TEMPLATE_SHEET)


    VarThisFileName = ActiveWorkbook.Name
    VarOtherPath = "C:\Documents and Settings\rjex\Desktop\"
    VarOtherFileName = "Data Centralv1.xls"
    VarOtherFullFileName = VarOtherPath & VarOtherFileName


    Workbooks.Open FileName:=VarOtherFullFileName
    Set myrange = Sheets(DATABASE_SHEET).Range("IDList")    'the lookuplist
    rowindex = Application.Match(irow, myrange, 0) + 1
    'gets the rowno of the case id in database...


    'the rest is easy


    Sheets(DATABASE_SHEET).Range("L" & rowindex).Value = mysheet.Range("SCORE").Value
    Sheets(DATABASE_SHEET).Range("M" & rowindex).Value = mysheet.Range("COM1").Value
    Sheets(DATABASE_SHEET).Range("N" & rowindex).Value = mysheet.Range("COM2").Value
    Sheets(DATABASE_SHEET).Range("O" & rowindex).Value = mysheet.Range("COM3").Value
    Sheets(DATABASE_SHEET).Range("P" & rowindex).Value = mysheet.Range("COM4").Value


    Windows(VarOtherFileName).Close SaveChanges:=True
    Windows(VarThisFileName).Activate


    Application.Goto Reference:="COM1"
    Selection.ClearContents
    Application.Goto Reference:="COM2"
    Selection.ClearContents
    Application.Goto Reference:="COM3"
    Selection.ClearContents
    Application.Goto Reference:="COM4"
    Selection.ClearContents


    'Sheets(TEMPLATE_SHEET).Range("COM1").ClearContents
    'Sheets(TEMPLATE_SHEET).Range("COM2").ClearContents
    'Sheets(TEMPLATE_SHEET).Range("COM3").ClearContents
    'Sheets(TEMPLATE_SHEET).Range("COM4").ClearContents


End Sub

Display More

rbjexcpa · Aug 7th 2003

Just perfect, thank you and also to Egad for laying the groundwork on this.

Rod

[Solved] VBA: Referencing another spreadsheet

Participate now!

Share

Users Viewing This Thread