Quick Links ▼

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Import data from a closed workbook

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

Import data from a closed workbook

Resolved · Medium Priority · Version 2003

Reply Thu 2 Jul 2009, 22:11Delegate Daren said...

Daren has attended:
Excel VBA Intro Intermediate course

Import data from a closed workbook

I require an excel sheet to inport data from another closed workbook
The programming below works fine, but the source date is currently placed within the active workbook, called "AreaName".
The information needs to be imported from the sheet1 on a closed Excel workbook

Can you help, Thank you

I nPublic Sub CmdButtonOK_Click()

SiteName = CmbSiteSelect

intTargetRowCount = 80 'This is the row 10 were the information is placed.

IntNumRows = Sheets(AreaName).Range("A2").CurrentRegion.Rows.Count
'Using 'current Region' selects the all the data starting from cell A on the 2nd
'row to the end of the data i,e to cell "J526"

For introwcount = 1 To IntNumRows 'This tell the count to move along by cell

If Sheets(AreaName).Range("A2").Cells(introwcount, 11).Value = SiteName Then
'look for the specified name, in row 11, in the range from cell A2 in the
'worksheet 'Poplar'.

For intcolumnCount = 8 To 23

Sheets("DataSheet").Cells(intTargetRowCount, intcolumnCount - 7).Value = _
Sheets(AreaName).Range("A2").Cells(introwcount, intcolumnCount).Value

Next intcolumnCount

intTargetRowCount = intTargetRowCount + 1

End If

Next introwcount

Unload UserForm1eed it

For upcoming training course dates see: Pricing & availability

Reply Tue 7 Jul 2009, 11:26Trainer Jacob said...

RE: Import data from a closed workbook

Hi Daren

Thanks for your post, I will need to run this by one of our VBA trainers, they are extremely busy at the moment but hope to catch up later today so we should be able to let you know if we can assist by tomorrow.

Kind regards

Jacob

Reply Wed 8 Jul 2009, 16:26Trainer Jacob said...

RE: Import data from a closed workbook

Hi Darren

I have discussed your post with one of my colleagues and to be reasonably confident we can assist will be beyond the scope of this forum.

I shall contact you directly so that you can send me a copy of your working file.

We can have a look at possible solutions, this may incur some development time, but we will let know first.

Kind regards

Jacob

Excel articles

· Making The Most Of Filtering In Excel
· Find Out Your Age In Days With Excel
· How To Create A Macro In Excel 2010
· Differences In Excel 2003, 2007 And 2010
· Microsoft Excel's Best Kept Secret

Please browse our web site to find out more about
excel training course and other Microsoft training courses.

Excel tip:

Reconciling a list to correspond with another

May have a list that needs to make but on another sheet one list seems to be out, eg. product list one sheet contains all product and inventory data, while the other contains pricing data. Both need to match with all products but there is more products in one list than the other. To find the disparaging product compare data in the two columns that need to match Make sure that order the same way.
Create another column in the sheet that has the most items and type in the first cell

=Exact(text1,text2) text1 being the cell that you want compared with text2 cell reference. Drag to filldown and your first false will give you for first cells that does not match. Correct insert the row with data in other sheet and continue the process until all the data returns true. Delete the column inserted.

View all Excel hints and tips