Macro for opening and copying

[Frank Situmorang]

Hello generous people:

Can we use macro for
1. Opening many files in order to copy a range from the files
2. Copying the ranges to my file which I called a "data bank"
Our purchase orders was typed in the excel, and the name of the files is the
name of the project. I want to take the line items to be used in my file in
order to know name of the materials and Unit price.
To be more details I explain what I do manually:
1. Open the file name abc.xls
2. copy from range a1....e50 to my data bank
3. Tommorrow there is an additional range in the same file abc.xls range
a51..e75, I copied it to my data bank file
4. the next day there is a project PQR, so there is a file for all P.O's in
this project named PQR.xls, I operned it and copy say range A1...E40.. to my
data bank.

my question is how can I create an VBA for this routine job. There are 2
things variable.

1. File name could increased
2. range is the file could change/variable.

I appreciate your idea on how to solve the problem. Clould it be a prompt
parameter to fill in the name of the file and the ranges? in the middle of
the process of macro when it is running?

Many thanks,

Frank

 

[steve_doc]

To answer some of your questions in general terms

1. Opening many files in order to copy a range from the files Yes
2. Copying the ranges to my file which I called a "data bank"

Yes

Due the variable nature of the file names as you stated, you could use an
User Prompt in your macro - but this requires that a user knows the exact
name of the file - same with the variable range.

Would it not be possible to implement a naming convention strategy?
eg. File name = abc1806.xls or DailyOrders1806.xls.

I am by no means saying that it is not possible, just that with variable
file names and ranges the chance of human error increases.

HTH

 

[Frank Situmorang]

Hi Steve:

To overcome the variable names of file and variable range, is there any way
in excel to have a macro and in the middle of the process user will do a
manual action, forexample see Rond de Bruin sample:
Sub RDB_Copy_Sheet()
Get_File_Names _
MyPath:="C:\Users\Ron\test", _
Subfolders:=False, _
ExtStr:="*.xl*"
macro will ask us to do manually the my path?
the same thing as the range. Like in lotus long time ago, if we make a macro:
Del{?), down, the user can streach down the cursur how many rows to delete.

We appreciate your help.

Frank,
Jakarta, Indonesia.

 

[Mike Fogleman]

Perhaps looking at Help on this function:
Application.GetOpenFilename

Mike F

 

[steve_doc]

Hi Frank

To specificallly answer your question in general terms - Yes there is
Declare a variable
Set the variable to an InputBox

eg
Dim stUserInPut As String
stUserInPut = InputBox("Please insert data here!")

This can be placed pretty much anywhere in your procedure.

However my concern would still be the possibility of introducing a User
error, and the impact this would have on the integrity and accuracy of your
data.

If it were possible to implement a naming convention as previously stated
the macro procedure would know where and which file to open. Variable ranges
can be handled with code given a few conditions

With the example that you gave re using the mouse to set the range in a
variable file, I am sure that that can be done - however its not something I
know how to do yet!

I guess what you need to ask yourself is what is the cost of sorting out
User Errors in your data vs the cost of implementing a consistent strategy
that code could deal with?

HTH

 

[Frank Situmorang]

Hi Steve:

I learned from your last e-mail, I prefer to have a naming convention, I
appreciate if you could explain and give the example of VBA for:
1. Naming convention in case we want tio open and update 200 workbooks,
because what I want to do to extract the line items of Purchase Orders which
were typed in Excel. Each project has many P.O, typed downwardly.

2. Examaple of working with ranges, because the process is we will do
updating project by prject, mybe that must be a subroutine?. From all these
P.O's line item will be put into a workbook called " historical Unit price
based on P.O". This will be used by purchasing clerck to negotiate with
vendors.

So the ranges should be a dynamic range, because the number of line items
changes every time

Many thanks again,

Frank

 

[steve_doc]

Hi Frank

Apologies for not posting sooner - just had a new project land on my work
table, so time is a little short.

I will do some digging and all being well will post some info for you over
the weekend.

Steve

 

[Frank Situmorang]

Hi Steve...
I appreciate your effort, I , from the developping country, Indonesia, feel
indebted to you all guys in the advanced countries. I have to admit that this
forum is very helpful to me and I can gain more lessons that we can apply in
our office works.

Greetings from Jakarta, Indonesia.

Frank