Solved: running dynamic data range from xml workbook into specific worksheet

[Beatrix]

Hi All ,

I have an xml workbook as a data source. What I need is running a dynamic data range from this xml workbook into a specific worksheet in xls file.

data should run into Year6 (starting row number A55). Also it should run from specific folder in C drive.

I have belove code however it is not working for this case :o


Sub RefreshData()

ActiveWorkbook.RefreshAll

Workbooks.Open Filename:="C:\Assessment\CB\CBSYr6.xml"

Sheets("Sheet1").Activate
Range(Cells(Range("b2").Value + 2, 1), Cells(Range("b2").Value + 2, 40)).Select
Selection.Copy
Sheets("Summary").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range(Cells(Range("b3").Value + 2, 1), Cells(Range("b3").Value + 2, 40)).Select
Selection.Copy
Sheets("Summary").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub
I attached sample xml and xls workbooks. Can anyone help me on this please ?

Cheers
Yeliz

[mancubus]

hey ya.

the code you posted is trying to copy some rows from Sheet1 to Summary (which doesn't exist in xml file). i think you want to copy rows to Summary ws in sample.xls


Range(Cells(Range("b2").Value + 2, 1), Cells(Range("b2").Value + 2, 40))

this expression does not evaluate to range object, because Range("b2").Value is a String, which is "F".


if you can post sample.xls file with before and after macro tables, it'll be much easier to help.

cheers.

[Beatrix]

Hiya ,

Thanks for your response. I want to copy the rows from Sheet1 in xml files to Year6 in sample.xls. (first row should be A55 highlighted in sample_after macro.xls below)

You're right below code is not right at all :o)

thanks very much for your time.

Cheers
Yeliz



hey ya.

the code you posted is trying to copy some rows from Sheet1 to Summary (which doesn't exist in xml file). i think you want to copy rows to Summary ws in sample.xls


Range(Cells(Range("b2").Value + 2, 1), Cells(Range("b2").Value + 2, 40))
this expression does not evaluate to range object, because Range("b2").Value is a String, which is "F".


if you can post sample.xls file with before and after macro tables, it'll be much easier to help.

cheers.

[mancubus]

hi.

i dont see any connections to external data sources, so i removed ActiveWorkbook.RefreshAll. if otherwise, you may keep it.

it seems data range to copy in xml file is Range("A1:AC55")


goes to a standard module in sample.xls

Sub RefreshData()

Dim dataWB As Workbook, dataWS As Worksheet
Dim baseRng As Range, dataRng As Range

Set baseRng = ThisWorkbook.Worksheets("Year6").Range("A55")
Set dataWB = Workbooks.Open("C:\Assessment\CB\CBSYr6.xml")
Set dataRng = dataWB.Worksheets("Sheet1").Range("A1:AC55")
Set baseRng = baseRng.Resize(dataRng.Rows.Count, dataRng.Columns.Count)

baseRng.Value = dataRng.Value

dataWB.Close False

End Sub

[mancubus]

noticing the "dynamic" in thread title;

you may change this line
Set dataRng = dataWB.Worksheets("Sheet1").Range("A1:AC55")

to:

With dataWB.Worksheets("Sheet1")
Set dataRng = .Range(.Range("A1"), .Range("A1").End(xlToRight))
Set dataRng = .Range(dataRng, dataRng.End(xlDown))
End With


it's like, after selecting A1, pressing ctrl+shift+right arrow then down arrow.

bottom rows of xml file contains some rows that will not be copied.

[Beatrix]

That's perfect! Thank you so much:friends:

Yep data range is dynamic so I changed the script line as you said.

Cheers




noticing the "dynamic" in thread title;

you may change this line
Set dataRng = dataWB.Worksheets("Sheet1").Range("A1:AC55")
to:

With dataWB.Worksheets("Sheet1")
Set dataRng = .Range(.Range("A1"), .Range("A1").End(xlToRight))
Set dataRng = .Range(dataRng, dataRng.End(xlDown))
End With

it's like, after selecting A1, pressing ctrl+shift+right arrow then down arrow.

bottom rows of xml file contains some rows that will not be copied.

[mancubus]

wellcome.

ps: Row 1 and Column A must not contain blank cells in order for xlToRight and xlDown to function properly.