Opening/getting data from different excel file

[yoinkster]

Hey guys,

I'm trying to write a macro that will open a particular excel file, copy a column and then close it again.

I can get it to go to the other workbook if it is open using

Windows("WorkbookName.csv").Activate

but can't seem to figure out how to get VBA to open the file itself.

Is it possible ?
Thanks.

[marshybid]

Hi there,

I believe you may be able to use an edited version of code provided to me by MD;

[vba]
Private Sub AddData()
Const FILEPATH As String = _
"Your filepath for the file containing the data you want e.g. C:\My Documents\Folder\"
Dim i As Long '// This is the column reference in the file
Dim k As Long '// This is the row reference
'Dim LastRow As Long

With Sheets("Name of destination file worksheet")
For i = 1 To 3 '//This would equate to Column A, B & C (1 to 3) if you only need one column then change to For i = 1

For k = 1 To 125 '//This would equate to rows 1 throiugh to 125, edit for your own purposes

.Cells(k, i).FormulaR1C1 = "='" & FILEPATH & _
"[Your data spreadsheet name.xls]name of sheet holding data'!R" & k & "C" & i
Next
Next
End With
End Sub
[/vba]

I haven't tested this, but it should do what you are asking for.

Hope this helps.

Marshybid

[yoinkster]

That is another way round of doing what I wanted yes and I can see how it would work but my problem is that the column I want to copy does not always have a fixed number of entries and I don't want the whole column either.

The reoprt I get out of our other system looks something like ::

SALES for Region A

Total
Product 1
Product 2
Product 3
etc.

SALES for Region B
Total
Product 1
Product 2
Product 3
etc.

and so on for each region, so the problem is I don't want all of column A, nor is the number of rows I want from Column A constant month on month as Product 2 may drop out with no sales or a new product mgiht enter the market.

My hope was that I could automatically open the file, select A7 {which is always the first instance of the word "Total" and use

Range(Selection, Selection.End(xlDown)).Select

to pick up the rest of the product list {as there is a break between regions} and then paste it into my report template.

What you suggested there would've been awesome and I would've been eternally greatful for it had my product list been fixed each month
I'm trying to avoid doing anything manual here as I will be handing this over to a client and I don't trust their abilities to either open everything necessary or to edit the macro and set the value of k probably had I gone with your suggestion

Has anyone else got any ideas ?

[marshybid]

Hi there,

you could just do the following;

For k = 7 To 65536

This would then include all cells in the column from row 7 to the bottom of the sheet, this will then paste them into your destination sheet including blanks

Alternatively make k a range

Marshybid

[Bob Phillips]

Use

[vba]

Range(Range("A7"),Cells(Rows.Count,"A").End(xlUp))
[/vba]

[marshybid]

Use

[vba]

Range(Range("A7"),Cells(Rows.Count,"A").End(xlUp))
[/vba]

Thank you xld

Marshybid

[yoinkster]

Use

[vba]

Range(Range("A7"),Cells(Rows.Count,"A").End(xlUp))
[/vba]

Thanks for that, I think I understand what that actually does but where would it fit in with what marshybid said ?
Or is what you said an alternative ? I'm not sure how what you said works, could you explain it to me please

[Bob Phillips]

No, it is intended as an alternative to what you proposed in post #3.