How To Import A Single Value From A Webpage Into Excell

%3CLINGO-SUB%20id%3D%22lingo-sub-920134%22%20slang%3D%22en-US%22%3EHow%20To%20Import%20A%20Single%20Value%20From%20A%20Webpage%20Into%20Excell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-920134%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20import%20the%20price%20of%20a%20single%20magic%20card%2C%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fwww.starcitygames.com%2Fcatalog%2Fmagic_the_gathering%2Fproduct%2F173366%2F213205%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.starcitygames.com%2Fcatalog%2Fmagic_the_gathering%2Fproduct%2F173366%2F213205%3C%2FA%3E%2C%20into%20excell%202010.%20Thought%20this%20would%20be%20fairly%20straightforward%20but%20it%20is%20not.%20Power%20Query%20cant%20even%20see%20that%20URL%20despite%20normal%20query%20being%20able%20to...%20The%20best%20i%20can%20do%20is%20power%20query%20this%20URL%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fwww.starcitygames.com%2Fresults%3Fname%3DLinvala%252C%2BKeeper%2Bof%2BSilence%26amp%3Bauto%3DY%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.starcitygames.com%2Fresults%3Fname%3DLinvala%252C%2BKeeper%2Bof%2BSilence%26amp%3Bauto%3DY%3C%2FA%3E%26nbsp%3Band%20select%20the%20value%20by%20removing%20the%20rows%20adn%20columns%2C%20but%20the%20price%20header%20is%20included.%20All%20i%20want%20is%20the%20price%20value%20on%20it's%20own%20without%20the%20header.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-920134%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-920158%22%20slang%3D%22en-US%22%3ERe%3A%20How%20To%20Import%20A%20Single%20Value%20From%20A%20Webpage%20Into%20Excell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-920158%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F429475%22%20target%3D%22_blank%22%3E%40Jamie_Small%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20you%20reach%20to%20the%20value%20after%20transforming%20the%20data%2C%20you%20may%20right%20click%20the%20value%20to%20drill%20down%20in%20order%20to%20remove%20the%20header%20in%20the%20power%20query%20editor%20itself%20but%20the%20point%20is%2C%20when%20you%20load%20it%20on%20to%20the%20sheet%2C%20since%20it%20would%20be%20loaded%20as%20an%20excel%20table%2C%20a%20default%20header%20would%20be%20added%20automatically.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-920747%22%20slang%3D%22en-US%22%3ERe%3A%20How%20To%20Import%20A%20Single%20Value%20From%20A%20Webpage%20Into%20Excell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-920747%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BYep%20thank%20you%20figured%20out%20you%20can%20hide%20the%20header%20in%20the%20table%20properties.%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20new%20problem%20is%20this%2C%20specifying%20the%20value%20purely%20from%20a%20single%20selection%20of%20the%20number%20of%20rows%20and%20columns%20wont%20work%20long%20term%20as%20card%20stocks%20change%20with%20time%20and%20new%20stock%20may%20be%20added%20or%20removed%20for%20versions%20and%20conditions%20above%20the%20card%20of%20interest%20changing%20the%20row%20its%20in.%20What%20i%20really%20need%20is%20to%20specify%20the%20set%20%22Rise%20of%20Eldrazi%22%20and%20the%20condition%20%22NM%2FM%22%20which%20will%20remain%20constant%20no%20matter%20what%20and%20select%20the%20value%20that%20matches%20those.%20Could%20you%20suggest%20a%20VBA%20code%20that%20could%20achieve%20this%20and%2For%20a%20place%20to%20learn%20the%20relevant%20syntax%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I'm trying to import the price of a single magic card, http://www.starcitygames.com/catalog/magic_the_gathering/product/173366/213205, into excell 2010. Thought this would be fairly straightforward but it is not. Power Query cant even see that URL despite normal query being able to... The best i can do is power query this URL http://www.starcitygames.com/results?name=Linvala%2C+Keeper+of+Silence&auto=Y and select the value by removing the rows adn columns, but the price header is included. All i want is the price value on it's own without the header. 

2 Replies
Highlighted

@Jamie_Small 

After you reach to the value after transforming the data, you may right click the value to drill down in order to remove the header in the power query editor itself but the point is, when you load it on to the sheet, since it would be loaded as an excel table, a default header would be added automatically.

Highlighted

@Subodh_Tiwari_sktneer Yep thank you figured out you can hide the header in the table properties.

My new problem is this, specifying the value purely from a single selection of the number of rows and columns wont work long term as card stocks change with time and new stock may be added or removed for versions and conditions above the card of interest changing the row its in. What i really need is to specify the set "Rise of Eldrazi" and the condition "NM/M" which will remain constant no matter what and select the value that matches those. Could you suggest a VBA code that could achieve this and/or a place to learn the relevant syntax?