External Excel spreadsheets and assorted VBA

Right, I need some help here as I’m well outside my comfort zone, I can count on the fingers of one foot the number of times I’ve had to use Excel in anger.

I have a large Excel spreadsheet from which I want to extract some data into another spreadsheet. The ultimate goal is to find (given value) in (given column), then copy (data in other specified columns) from the row where (value) was found.

I’ve made a connection to the remote spreadsheet as per the instructions here:

https://support.office.com/en-us/article/Connect-data-in-another-workbook-to-your-workbook-3a557ddb-70f3-400b-b48c-0c86ce62b4f5

I don’t know it this is a Best Practice but it made sense to me logically to have the data connection at a ‘user’ level rather than buried in code in case someone moves the data file.

However, I can’t for the life of me work out how to reference that connected sheet. I’ve tried all manner of syntax in VBA and I just get variations on “syntax error” and “subscript out of range.” Now I’ve just put down the VBA editor, and I can’t even suss how to reference a cell using a formula format like =Sheet1!A1. What am I missing? A brain?

Eventually I want to reference the remote sheet using named Ranges (in case someone cocks about with it), but for the moment just grabbing an absolute cell’s value would get me going. I think I can cruft the rest of the code from there.

Cheers, etc.

You are molgrips and I claim my five pounds.

I think you might be overcomplicating this.
Open both spreadsheets in the same session of Excel.
In the cell where you want the data to end up type = then navigate to the cell in the other sheet from whence you wish to fetch the data. Click on it and press enter.
Excel does the rest for you and, as long as the donor sheet doesn’t change location, it’ll work.

Try:

='[<filename.xlsx>]<Sheet name>’!<cell>

Open both spreadsheets in the same session of Excel.

That’s the thing, I don’t want to open both spreadsheets. The finished sheet will be going to users, so I just want a ‘get data’ button.

='[<filename.xlsx>]<Sheet name>’!<cell>

That does actually work (I sussed that about five minutes ago), but I’m trying to avoid hard-coding the filename. Ie, it’s using the absolute pathname rather than the data connection I’ve specified. I don’t have control over the source and can’t trust the owners not to frob about with it.

If you want to just send the data, copy the content of the whole sheet and “paste values” back over itself.

If it’s an ongoing thing, you can get a macro/vba to do that for you but you’ll need the name of the sheet in there somewhere…

That’s the thing, I don’t want to open both spreadsheets. The finished sheet will be going to users, so I just want a ‘get data’ button.

Both sheets don’t need to be open for the link to work, only to facilitate easy construction of the link.

Once the link is made , Excel will update the data without opening the remote sheet which can be protected in any case to prevent users dicking about with it.

And therein lies the problem.

I’m not interested in cutting and pasting anything. That’s what they already do, and the reason I’m trying to automate the process in the first place. We have computers precisely so that we don’t have to do manual tasks.

But if you use a relative path and a filename there’s no copying/pasting involved – OK, you need to ensure that file “A” and file “B” are in the same (relative) place and that the name hasn’t changed, but that’s not IMO too unreasonable a requirement.

can’t trust the owners not to frob about with it.

Then don’t use Excel, as whatever you do they’ll always be able to disconnect the sheet from your datasource and frob it up.

No, and ultimately this whole mess needs fixing with a database and a nice friendly web front end, but that involves a lot more work (and moreover, a lot of interested parties all with their own ideas and requirements). I was just hoping to sticking-plaster the existing system as a stop-gap.

HLookup????????

You have to specify CELL and ROW value (A1 fer instance)

This article describes the formula syntax and usage of the HLOOKUP function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

Description
Searches for a value in the top row of a table or an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.

But I guess you’ve discounted that or it’s not what you need..

I was just hoping to sticking-plaster the existing system as a stop-gap.

And that’s how it begins….

(Sorry can’t help you with actual advice, although I would if I could. But then as Perchypanther points out, I have a reputation to keep up)

EDIT You want your macro to actually copy the data across, right?

*ring ring*….*ring ring*….

Hello?…….
Yes this is Melinda Gates…..
Hang on , i’ll see if he’s in…….
BIIIILLLLL! ……
Sorry, I think he’s in the bath……
BILL!…..There’s some bloke called Cougar on the phone for you…..
Yeah,….. wants to talk to you about Excel……..
Bag o’ shite apparently…….
What’s that Cougar?……
OK, I’ll tell him…….
BIIILLL!,…. Cougars’ mate molgrips wants a word about Word as well…

SELECT column_name,column_name,etc.
FROM table_name;

Blah di Blah..

Ok, so thats all I remember about SQL, but VBA isn’t too dissimilar..

Google’s yer mate here I wreckon.

Hi All,

Please help me out with a VBA code to select all the data on a worksheet.
I am using the following code:

Sheets(1).Select
Range(“A1”).Select
On Error Resume Next
mylastrow = Cells.Find(“*”, [a1], , , xlByRows, xlPrevious).Row
mylastcol = Cells.Find(“*”, [a1], , , xlByColumns, xlPrevious).Column
mylastcell = Cells(mylastrow, mylastcol).Address
myrange = “A2:” & mylastcell
Range(myrange).Select
Selection.Copy

From MrExcel

Google’s yer mate here I wreckon.

Chip Pearson’s my go-to guy for hard stuff…

http://www.cpearson.com/Excel/Topic.aspx

EDIT You want your macro to actually copy the data across, right?

Yep. Once the data’s in it doesn’t need to update again from the main spreadsheet. Quite the opposite in fact, it needs to stand alone without relying on the data source (unless the macro is re-run).

But I guess you’ve discounted that or it’s not what you need..

It’s not what I asked. The problem isn’t “how to do a lookup,” it’s “how do I reference data in a connected spreadsheet data source.” I’ve already written a chunk of VBA to manipulate the current workbook, that’s not an issue.

I used to do loads of this shite, I actually found it quite interesting at one point in my sad life. But it’s been 10years since and I got bored…
But I think VBA is the best way to go, it’s quite flexible (IIRC) and really not too complicated at all.. By that I mean if I can create rudimentary dB’s linked to Excel via VBA for monthly FinRep reports then hey, you can do it FoSho.

Ahh, I knew you were a geekok with VBA.. in that case I’m oot as you’re far better than me at it then.

And I kinda knew lookups were/are a blunt instrument.

Surely then it’s a case of running a macro where a) the macro gets the value, b) sets a variable then c) puts the variable value into the other sheet?

It’s a) that you are having trouble with?

I would be tempted to use a pivot table from the remote data.
Then just right click “refresh” as required

I’m oot as you’re far better than me at it then.

I’m probably not. I’ve got a decent grounding in generic programming, but the last time I used VB was writing an Intranet site where the server was NT4 and the client was IE3. The amount of VBA I’ve done is as close to “none” as makes no odds so whilst I’m quite happy to Google and Cruft I’m totally at sea with all the Excel object handling.

It’s a) that you are having trouble with?

The problem is a) in so far as a) is not an open spreadsheet nor a hard-coded URL. It’s a data connection as per the link I posted back in the OP. How do I reference that connected data?

For this sort of thing I switch on the Macro (now VB) recorder and then do a bunch of things. I would switch it on and then follow the original instructions you did to create a data connection and then I would paste that connection into a new sheet. I would then go to a cell elsewhere and reference a cell in the pasted data using ‘=’. Then I would switch off the macro recorder and see what was created. That’s usually enough to get going

Can I only get at it from the Data ribbon, is that the problem? Ie, I’ve not actually imported anything at this point? So the way I’ve connected it is a shot to nothing?

I wonder then if the way to go would be to have the source filename in a cell on my workbook. Then I can read it with VBA and the user can change it if the data file moves. Hmm.

Just tried it and it refers to the cell in the sheet rather than in the data connection 🙁

I wonder then if the way to go would be to have the source filename in a cell on my workbook. Then I can read it with VBA and the user can change it if the data file moves.

You could do that and even get vba to check if it exists and ask the user to select a new file if it doesn’t exist. PITA though

Off repeated, always ignored: never use external links.

Ever.

Now I’ve got that out of the way, you should be able to query one wbk from another. It’s also poss to have wks level parameters set up.

The problem is a) in so far as a) is not an open spreadsheet nor a hard-coded URL. It’s a data connection as per the link I posted back in the OP. How do I reference that connected data?

ODBC?

Heh. I was contemplating cracking out ADO for a minute.

As I understand it your problem isn’t the HLOOKUP part, it’s that you don’t want to have a hardcoded filename in the HLOOKUP.

So, you could just use an =INDIRECT() function to ‘evaluate’ an HLOOKUP and build the HLOOKUP statement string using CONCATENATE and a cell that contains the sourcefilename. It’s a bit horrible and I wouldn’t want to do it for more that one lookup but it would work and wouldn’t involve VBA

Just had a play following leffeboy’s suggestion – and you should just be able to read the source data table without needing to reference anything else:

“MyTableName[[#Headers],[Incident tracker]]”

Here MyTableName is the name of the table in the source workbook.

That said, following the link you originally posted – the connection you create includes the filename anyway, not sure if you’re really gaining anything over my initial suggestion.

In your vba you need to open the other spreadsheet as a workbook object, something like

Dim wbk as workbook
Set wbk = workbooks. Open (filenamd) or maybe application. Open, I’m free styling here

Then you can reference it by

X = wbk.sheets(name).cells(row, column)

Don’t forget your error handling, and always smart to open it read only unless you’re going to be making changes.

yep, just open the other Workbook (you can do this all hidden), suck out the values you want and then close it (all without anyone knowing).

Eg

The following code example imports a sheet from another workbook onto a new sheet in the current workbook. Sheet1 in the current workbook must contain the path name of the workbook to import in cell D3, the file name in cell D4, and the worksheet name in cell D5. The imported worksheet is inserted after Sheet1 in the current workbook.
VBA

Sub ImportWorksheet()
‘ This macro will import a file into this workbook
Sheets(“Sheet1”).Select
PathName = Range(“D3”).Value
Filename = Range(“D4”).Value
TabName = Range(“D5”).Value
ControlFile = ActiveWorkbook.Name
Workbooks.Open Filename:=PathName & Filename
ActiveSheet.Name = TabName
Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1)
Windows(Filename).Activate
ActiveWorkbook.Close SaveChanges:=False
Windows(ControlFile).Activate
End Sub

https://msdn.microsoft.com/en-us/library/office/ff194819.aspx

Once you’ve got it working, you can hide the opening using
Application.ScreenUpdating=False
and then set back to True once you’re done, that way the user doesn’t get to see Excel opening things and copying worksheets etc

Yeah. I’ve got a prototype mostly working now, I bit the bullet and hardwired the filename in the code. There’s a whole block of data cleansing and validation I want to do, but I can worry about that once it actually works.

If anyone is really interested I can share the code then you can all have a laugh suggest any improvements. It’s not particularly exciting though.

Cheers all.

I’m free styling here

😆

I’m going to use that.

I bit the bullet and hardwired the filename in the code.

Can’t you put the filename in a cell? Or you can have a pop up when the macro is run to prompt for it?

Sub GetOpenFile()

Dim fileStr As String

fileStr = Application.GetOpenFilename()

If fileStr = “False” Then Exit Sub

Workbooks.Open fileStr

End Sub

Can’t you put the filename in a cell?

Yeah, I’m going to. This way was just quicker whilst I was trying to get it working.

Prompting isn’t going to work, the source path is a massively long Sharepoint breadcrumb trail. EDIT – though it might work if I set the default directory for the dialogue to its current location. Good thinking.

One tip is to either stick “Option Explicit” at the top of each code module or enable it in defaults – it means you have to define each variable before you use it, which saves loads of hassle as miss-spellings get picked up by the IDE rather than just creating new miss-spelt variables with no value.

Handy, ta.

though it might work if I set the default directory for the dialogue to its current location. Good thinking

There is another post on setting default directories… This one..

Good to see you’ve moved it on a bit.

I’ve found when using the record function you can strip out a load of commands to make it run faster etc.

Ref the file location is one cell is, inspired.

What I’ve done so far is create a test Excel sheet with two columns, each a named range somewhat arbitrarily “numbers” and “letters.” The VBA prompts for a “number”, looks it up and returns the corresponding “letter.”

There’s no error checking at all as yet. I need to handle ‘not found’ and exit cleanly. I guess “close file” needs to be a separate sub that I can exit to regardless (rather than an ugly GoTo)? Then it’s a case of putting the main sub on a button, pointing the code at the actual data source, pulling in actual data and applying some data validation.

Code to follow.


Option Explicit

Sub getData()

Dim sourceFile As String 'File/pathname of data source
Dim wbSource As Workbook 'source workbook object
Dim wsSource As Worksheet 'source worksheet object
Dim numbers As Range 'named range in source
Dim letters As Range 'named range in source
Dim location As Range 'location of searched-for cell
Dim letter As String
Dim number As Variant

'Open source workbook "hidden"
Application.ScreenUpdating = False
sourceFile = "\\absolute-pathname\test.xlsx"
Set wbSource = Workbooks.Open(sourceFile)
ActiveWindow.Visible = False
ThisWorkbook.Activate
Application.ScreenUpdating = True

'define source variables
Set wsSource = wbSource.Worksheets("Sheet1")
Set numbers = wsSource.Range("numbers")
Set letters = wsSource.Range("letters")

'get key field as String
number = Application.InputBox("Enter number", Type:=3)

'find data and write back to local sheet
Set location = numbers.Find(What:=number)
letter = wsSource.Cells(location.Row, letters.Column).Value
ThisWorkbook.Worksheets("Version").Cells(6, 1).Value = letter
wbSource.Saved = True
wbSource.Close

End Sub

IANAprogrammer. Anything glaringly stupid there, please let me know. I did have the source opening read-only at one point but that seems to have disappeared during my buggering about with it, I need to put that back in.

I need to read up on variable types at some point, all this ‘Range’ and ‘Variant’ business is new to me and the strong typing is making my head spin.