Mimic 'Import external data' functionality

[thestappa]

Hello,

I am trying to get a button in a workbook to import data from a different excel file. I want it to mimic the way the 'Import external data' works but have a few reasons for doing it with VBA.

Reason 1. Customer wants it that way. Feels that it will be eaier to use if the users just have to hit a buttons as opposed to Data -> Import external data etc...


Reason 2. I need more control over the worksheet being imported. It can't just be a straight import, i need to do a few things to the data beforehand.

So I am looking for help to pick the file I want to open, pick the worksheet (if possible) then load the worksheet so I can manipulate it with VBA and then print it out in the existing worksheet. I can handle the manipulation part it just getting from point A to point B that seems to be giving me the most trouble.

Thanks.


*******************edit******************************
I should add I am going for a seamless feel. So I don't want to open (on the screen) the spreadsheet I am importing from nor do I want to copy and paste date into a worksheet and then update it. I would like to do my work to the data before it is entered.

[ravishankar]

Hi
Need more info. layout of your current workbook, what transformations you wish to carry out on the data and how you want the output to be presented
Ravi

[thestappa]

Thanks for the reply. I don't need help with that portion of the project. What I need help with is getting it so I can open the worksheet to work with it in VBA. If it were a worksheet in the same excel file I would just select it and do the work, but it's in a different excel file.

I did something similar with text files in PHP a while back. In the code, I would open the text file and could read through it and manipulate it all in the code. I want to be able to open an excel to be read by the vba.

I'm going for a seamless approach. Hopefully the second spreadsheet doesn't open.

User clicks import button
User picks file (Application.GetOpenFilename???)
User picks worksheet (not necessary, but would like)
Worksheet is selected for edit in VBA
Data is cleaned
Data is printed to spreadsheet

I need help with the bolded steps.

[ravishankar]

Hi
Does the attached file meet your expectation?
Ravi

[thestappa]

Hi
Does the attached file meet your expectation?
Ravi

It's close, but missing a part that i need.

An easy example of what I would like to accomplish is take a list like below from a second spreadsheet (assume in cells A1 to A6)

10
5
9
4
3
6

when i push the button in the destination file, I would pick the excel file and it would print that list to cells A11 to A15 with all values times two.

so the final would start in A10, instead of A1

20
10
18
8
6
12

I know how to have VBA start printing at A11 and do the math, I can't figure out how to get the file so that I can manipulate it with VBA before it shows up in the destination file.