[Closed] External Excel spreadsheets and assorted VBA

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...

[url= http://www.cpearson.com/Excel/Topic.aspx ]http://www.cpearson.com/Excel/Topic.aspx[/url]

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 [u]I[/u] 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 [s]a geek[/s]ok 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.

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 [i]MyTableName[/i] 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 [s]all have a laugh[/s] 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... [url= http://www.mrexcel.com/forum/excel-questions/526431-visual-basic-applications-pop-up-window-choose-file.html ]This one..[/url]

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.

[code]
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 = "\\[i]absolute-pathname[/i]\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
[/code]

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.

Could error trap location e.g.

If not location is Nothing then
do stuff
else
msgbox("Can't find blah")
end if

Also, add an error handler to make sure you always undo ScreenUpdating as otherwise it will barf and leave the screen locked so at top of function

On Error Goto ErrHandler

at bottom add:

Exit Sub

ErrHandler:
On error resume next
Msgbox("Oops, all gone tits up")
Application.screenupdating = true

end sub

Oh, is 'Nothing' a thing? IDNKT. Is that the same as Null or something else?

And yeah, shoving the whole thing in an If clause is the obvious way of doing it, schoolboy error. In my defence, I've been trying to write this all day whilst being interrupted every ten minutes. I'm shirking from home tomorrow so should be able to be a bit more focused.

You probably need to handle wbsource errors in case it doesn't exist eg
(After the open)
If wbsource is nothing then
Msgbox ("source missing", vbokonly+vbexclamation, "File errror")
Else
All your other stuff
End if

Oh, is 'Nothing' a thing? IDNKT. Is that the same as Null or something else?

Yep and you use "is" rather than "=" to test.

You probably need to handle wbsource errors in case it doesn't exist eg

Good point, though it's far more likely that a source change will be "we're using a new file" rather than "we've deleted the old one." Though ofc it needs the network connection to the source so that needs to be trapped.

Another trick, to stop it prompting the user to save the workbook you've just opened is:

Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

Then add "Application.DisplayAlerts = True" to the error handler as well.

Also, in the error handler you can add debug e.g.

Msgbox("Oops, error was " & Err.description)

Another trick, to stop it prompting the user to save the workbook you've just opened is:

I did that with wbSource.Saved = True - is that way any better / worse / different to this one?

(WhyTF it wants to save an unchanged sheet in the first place is beyond me...)

And you can speed it up by setting application. Calculation to manual at the top and back to automatic at the bottom, especially if you're shifting stuff that is then used in other formulae.

Sorry, what's that do? (Yes, I should Google but I'm eating...!)

Application.Calculation controls whether excel recalculates whenever anything changes or not. So if you set it to manual you can make changes without any overheads, then set it back to automatic at the end of your routine and it'll do all the sums once. I think the values are xlcalculationmanual or somessuch but the options pop up in the vba editor when you're typing so I'm sure you'll work it out 😉

I did that with wbSource.Saved = True - is that way any better / worse / different to this one?

Not uses that one myself. With Excel / VBA there are normally many ways of doing any one thing, so you just settle on what you've used before....

I found a more elegant way of doing it than both of these. You can add SaveChanges:=False to the .Close statement and it releases without prompting.

Anyway. Cooking with gas now, got the thing doing mostly what I want, and hooked it into an event handler so it automatically fires the script when the key field is updated. Chuffed with that.

The only major glitch I've got outstanding is that the data file is accessed via a UNC pathname which is actually a Sharepoint server. On some clients, the users are prompted for their Windows credentials if they open the data file manually via Sharepoint. On those clients, the script fails. If they open the file, supply credentials and then close it again, it caches the credentials and everything works again. This seems to be a bug / configuration error on some PCs and I can't readily work out how to get it to prompt for credentials. Hmm.

Anyway. Cooking with gas now, got the thing doing mostly what I want, and hooked it into an event handler so it automatically fires the script when the key field is updated. Chuffed with that.

If you have a function called by an event handler, always a good idea to add in Application.EnableEvents = False at the top and undo it at exit (and in the error handler). This will stop any unintended recursion eg if the function over writes a cell, which triggers a 'cell change' event etc...

On some clients, the users are prompted for their Windows credentials if they open the data file manually via Sharepoint. On those clients, the script fails.

You'll be able to ask the OS in VBA and determine this in advance - have a google, then yuo can trap it and prompt the user before hand.

always a good idea to add in Application.EnableEvents = False at the top and undo it at exit (and in the error handler).

Exactly what I did. I was doing it as a wrapper every time I wrote to that cell, figured it was more elegant to just put one at the start and end of the main sub, then got legged up when I tripped an Exit Sub call earlier in the routine (right when I was showing a colleague, for added yuks).

You'll be able to ask the OS in VBA and determine this in advance - have a google, then yuo can trap it and prompt the user before hand.

Yeah, I think I need to be on a symptomatic PC to test it properly, Google's giving a lot of conflicting information.

Whilst the crew are in, is there any way to search for a sheet in a workbook by name? This workbook has about 60 sheets and they aren't in alphabetical order.

Workbooks.<sheetname> innit.

then got legged up when I tripped an Exit Sub call earlier in the routine

best to use "Goto EarlyExit" and only have a single exit point

and then define

EarlyExit:

clean up stuff

Exit Sub

ErrHandler:

Clean up more stuff

End Sub

Workbooks.<sheetname> innit.

You'd have to trap that as it may not work.

Something like..

Public Function FindWSIndex(ByVal Name as String) As Integer

For FindWSIndex = 1 to Worksheets.count
if Worksheets(FindWSIndex).name=Name then
Exit function
Endif
next n

# We failed
FindWSIndex=0

End Function

Ta, will consider adding it cos it's not my document 🙂

best to use "Goto EarlyExit"

I was always taught goto = bad, is that not the case in the 21st Century?

You can also do

Public function findws (name as string ) as worksheet
Dim ws as worksheet
For each ws in thisworkbook.worksheets
If ws.Name = name then
Set findws = ws
End if
Next ws
End function

That (or something like it, free styling avain) will return the worksheet object so you can work with it directly.

I was always taught goto = bad, is that not the case in the 21st Century?

People used to stay that to stop kids writing spaghetti code in BASIC on their Spectrums. Like all tools, it can be used or mis-used. Otherwise the people who made the language (who are bigger geeks than us, most likely) wouldn't have taken the time to put it in there.

Anyone who gives out hard and fast rules about programming doesn't understand it properly.

Oh..wait.. shit.