Macros help!

[Carlos]

Hi, I am a computer science student, from Portugal, and i need help to Make
KML Files with Excel VBA.

In this part:

For Each cell In [Data2!A6.A50001]
pmName = cell.Offset(0, 2)
longitudeValue = cell.Offset(0, 1)
latitudeValue = cell.Offset(0, 0)

nembalagens2 = cell.Offset(0, 5)
totalcompart2 = cell.Offset(0, 6)

I need to change "For Each cell In [Data2!A6.A50001]" into something like
this: "For Each cell In ['C:\programs Files\Excel\NameOfBook.xlsm'
Data2!A6.A50001], because i want acess to another excel file from the file
where is the macro.

is it possible?

What is the sintax to do this?

Thanks!

 

[Dave Peterson]

You have to have that other workbook open...

Dim WkbkName as string
dim wkbkPath as string
dim Wkbk as workbook
dim myCell as range
dim myRng as range

wkbkpath = "C:\programs Files\Excel\" '<--include that trailing \
wkbkname = "NameOfBook.xlsm"

'check to see if the workbook is open
set wkbk = nothing
on error resume next
set wkbk = workbooks(wkbkname)
on error goto 0

if wkbk is nothing then
'not open, so open it
set wkbk = workbooks.open(filename:=wkbkpath & wkbkname)
end if

with wkbk
set myrng = .range("a6",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
'do your stuff
next mycell

Hi, I am a computer science student, from Portugal, and i need help to Make
KML Files with Excel VBA.

In this part:

For Each cell In [Data2!A6.A50001]
pmName = cell.Offset(0, 2)
longitudeValue = cell.Offset(0, 1)
latitudeValue = cell.Offset(0, 0)

nembalagens2 = cell.Offset(0, 5)
totalcompart2 = cell.Offset(0, 6)

I need to change "For Each cell In [Data2!A6.A50001]" into something like
this: "For Each cell In ['C:\programs Files\Excel\NameOfBook.xlsm'
Data2!A6.A50001], because i want acess to another excel file from the file
where is the macro.

is it possible?

What is the sintax to do this?

Thanks!