Accessing external workbook from VBA?

[IndigoDragon]

Hi there,

[Excel]
I'd like to access info in an external workbook from vba. As a sheet formula this would be "='MyPath[MyWorkbook.xls]MySheet'!$A$2".
In VBA I just can't seem to get it to work. The notation without "='" isn't accepted.

Any tips or is this only possible in VBA with ADO?

ThanX, cYa!

 

[SkipVought]

Hi,

1.

sPath = ActiveWorkbook.Path

2. You can also use MA Query via worksheet menu item Data/Get External Data/New database Query - Excel Files -- YOU WORKBOOK -- YOUR WORKSHEET.......

Works well with one table per sheet.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[IndigoDragon]

Hi SkipVought,
Thanx for your reply!

I know the path, the name of the workbook, of the sheet and I know the range. However, the name of the workbook will not always be the same so I can't link the workbooks or set up a fixed DB query. Right?
So I'd like to access the info by constructing a 'connection string' as you would in a sheet. ("='MyPath[MyWorkbook.xls]MySheet'!$A$2")

Is this at all possible?

 

[SkipVought]

If you macro record EDITING the QueryTable, you will see where the path and workbook name are stored BOTH in the connect string and the SQL.

With simple string variables, you can change the path/workbook and the sql.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[IndigoDragon]

Hi SkipVought,

I set up an ADO connection to retreive the data. Works fine except for: only the first two column from the sheet are imported. I've set "SELECT * FROM [Data$];" as the sql string but still...

What's the problem here?

 

[SkipVought]

Any tips or is this only possible in VBA with ADO?

2. You can also use MS Query via worksheet menu item Data/Get External Data/New database Query - Excel Files -- YOU WORKBOOK -- YOUR WORKSHEET.......

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[IndigoDragon]

SkipVought,

Yeah, well, I could, however Excel keeps crashing after selecting New Database Query. Nothing you can do about; I guess some native scripts are busted 'cos the help menu sometimes give a runtime error as well... Time for a fresh installation I guess.

But I've got it covered (At least, I thought.) with the ADO connection.

How'bout the ADO connection with sql "SELECT *" only importing the first two columns?

 

[IndigoDragon]

Hmmm, dit some testing and it seems the problem is the linebreaks in the contents of the third column are causing this, you know: the linebreaks shown as 'small squares'. I've been wondering earlier about how to remove these because they're also show when incorporating the text somewhere else.
I could post a new thread for this...but perhaps it's just about "flickin' a switch"...

 

[SkipVought]

in that column try substituting a space for vbLF

Replace([ColC],vbLF," ")

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[IndigoDragon]

Hey Skip,

This may be a stupid question, but the Replace() function doesn't accept anything other than strings in for the first argument...?

 

[SkipVought]

True. Use it in your SQL.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}