Input and calling data from another workbook

Category: excel for developers


MrGuinnss on Wed, 21 Dec 2016 00:49:25

I have an exported CSV file from my database with ~ 30 columns and 5000 rows in it.

I want to open a separate workbook that asks me for an item ID, goes into a specific column in the CSV file, finds all the rows with that item ID, and returns the item ID and 5 additional columns on those rows with the specific item ID.

My item ID's are alpha-numeric, and some have hyphens as well.

Can anyone assist or show me how I can get started? I'm well versed in excel, but I have no idea how to setup a separate workbook that would ask me for an item id, and then go searching for the info I explained above. 



Yuki Sun on Thu, 22 Dec 2016 03:07:13

Hi MrGuinnss,

Thanks for posting your query in our forum.

I’ve read through your thread and I am afraid the build-in function in Excel cannot be used to achieve the goal. However, I’ve consulted an engineer who supports VBA related questions in another forum and confirmed that your requirement that "a separate workbook that asks me for an item ID" might be achieved via coding. I’ll move your question to the following dedicated MSDN forum for Excel. Hope you will find a solution there:

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

Thank you for your understanding.

Best Regards,
Yuki Sun

MrGuinnss on Thu, 22 Dec 2016 17:43:49

Thank you! I hope someone knowledgeable replies...

Chenchen Li on Fri, 23 Dec 2016 02:52:53


>>how to setup a separate workbook that would ask me for an item id

The normal steps should be:

1)Open the CSV file.

2)Run the macro

3)Input the item ID

4)Output the row into a new workbook.

Please see the demo below, if you have any issue, please feel free to let me know.

Sub Demo()
Dim answer As Integer
Dim newWB As Workbook
Dim newWS As Worksheet
rep: myItemID = Application.InputBox("Enter an ItemID")
Set c = ActiveSheet.Columns("A").Find(myItemID, LookIn:=xlValues)
If c Is Nothing Then
answer = MsgBox("Cannot be found,do you want to re-enter?", vbYesNo + vbQuestion)
    If answer = vbYes Then
    GoTo rep
    Exit Sub
    End If
Application.Goto Reference:=Range(c.Address)
Set rng1 = Range(ActiveCell, ActiveCell.Offset(0, 3)).Offset(1 - ActiveCell.Row, 0)
Set rng2 = Range(ActiveCell, ActiveCell.Offset(0, 3))
Set uRng = Application.Union(rng1, rng2)
Set newWB = Application.Workbooks.Add
Set newWS = newWB.Sheets(1)
fileSaveName = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xlsx), *")
newWS.SaveAs (fileSaveName)
End If
End Sub