PDA

View Full Version : Importing into excel



yarsmith
11-10-2008, 08:42 PM
Can someone please point me to and/or suggest some code that will allow me to import data from one excel workbook into another please? I'm sure this question has been answered more than once, but try as I might, I can't seem to find it. Am I asking the question/searching incorrectly?

Currently, workbook #1 gets filled out with data and workbook #2 is used to write a report. The workbook #1 gets new data input and a new filename for each instance, but all the cell references stay the same from time to time. I'd like to just run a macro that prompts the user for the name/location of the worksheet to open, and then imports the pre-determined cells and ranges.

My curent solution is to link cells to an existing file named "import.xls" so I just take the new workbooks and do a saveas and overwrite the existing "import.xls" everytime. Then when I open the Workbook #2 for reports, it updates from the "import.xls" and I get the new data. This method works, but it's a bit clumsy and it isn't easily portable due to the filepath references in the macro.

Any help/direction is greatly appreciated. TIA !

GTO
11-10-2008, 10:01 PM
Greetings yarsmith,:)

Importing data can be done several ways, so let's consider what your particular parameters or circumstances are:

For ease of understanding, I'm going to refer to any of the workbooks you might be looking to get the data from as "VariableWB" and your workbook #2 simply as "ReportWB".


Are both workbooks in the same folder?
If not, are the VariableWB workbooks always in the same folder, or do we need to worry about finding the path?
You mention that the cells are always the same (thanks), just wanted to confirm that its just one sheet in both the VariableWB and the ReportWB?Finally, a copy or examples of both workbooks attached just makes it a lot easier to answer. If examples, make sure to delete private/company/proprietary info. Just need a bit of specifics...

Mark

yarsmith
11-12-2008, 01:00 AM
Thanks for the response Mark. This only allows me 1 attachment, so I'm using a stripped down sample version of the data input workbook ("VariableWB"). The proprietary issue as you mentioned, is the whole reason for my question and makes it difficult to post a decent example. What I need is to take the data from our input sheet and put it into another spreadsheet that I can then send to a client with none of the proprietary junk.

So for instance, on a single worksheet in the "ReportWB" I'd like to import the data from Cells A3 : J10 on worksheet "Location 1" and the identical cell range from the worksheet "Location 2" and so on (5 worksheets total). The VariableWB format never changes, but the location and name of the file does. My ReportWB re-formats and does all the charts and graphs that I want, i just need the new data imported into it.

To answer your questions:

1. Both workbooks are NOT in the same folder, unless I make a copy and put it on my desktop. The new data workbook is always saved in a different job folder with an ever-changing corresponding filename
2. I do need to worry about finding the filepath & this is where I got hung up on this project. Hence my clumsy solution of copying each new VariableWB and pasting it over a predetermined filename already on my desktop so that my name/location references in the ReportWB would work over and over again.
3. There are multiple sheets in the workbook, but every sheet is identical and needs the same data cells exported, the worksheets are just named different "location 1, location 2," etc.

So here's my fantasy ReportWB solution that I'm seeking:

Upon opening the ReportWB, a messagebox opens and queries the filename and file path to import, then imports the data from A3 : J10 on each worksheet.

Hope that clarifies my question. Thanks again for your help!

Bob Phillips
11-12-2008, 02:23 AM
This should get you started



Public Sub GetData()
Dim this As Worksheet
Dim Filename As String
Dim wb As Workbook
Dim sh As Worksheet
Dim NextRow As Long

Set this = ActiveSheet
NextRow = this.Cells(this.Rows.Count, "A").End(xlUp).Row + 1

With Application.FileDialog(msoFileDialogOpen)

.AllowMultiSelect = False
.InitialFileName = "*.xls"
If .Show <> -1 Then

Exit Sub
Else

Filename = .SelectedItems(1)
Set wb = Workbooks.Open(Filename)

For Each sh In wb.Worksheets

sh.Range("A3:J10").Copy this.Cells(NextRow, "A")
NextRow = NextRow + 8
Next sh
End If
End With

End Sub

RonMcK
11-12-2008, 07:44 AM
... This only allows me 1 attachment, so I'm using a stripped down sample version of the data input workbook ("VariableWB"). ...

yarsmith,

Since .zip is one of the supported file extensions, you can always zip up multiple files and upload them as a single entity.

Cheers,

yarsmith
11-13-2008, 12:54 AM
Thanks all. The sample code you provided gives me a great starting point - that will keep me busy for a couple days & I'll re-post my solution/mess. Thanks again!