We help IT Professionals succeed at work.

Excel Macro

taverny
taverny asked
on
Medium Priority
375 Views
Last Modified: 2012-06-22
Hi Experts,
I have an excel doc, with about 10 sheets. each sheets have 3 columns of datas .
First column is the Job# , the second is the Date Received, and the third is the Invoice Date.

What I am trying to accomplish is to get the networking days between the 2 dates( =networkdays formula) , So of course I know how to do it by going  to the first cell of a new column , type the formula and copy and paste the formula for the remaining of the column.
But I would like to create a macro that would do that for me for all the sheets.
Also the data comes from an Access Database which means that the excel spreadsheet gets overwriten everytime we run the query in ACCESS ( Access delete the Excel files and replace it with a new one) so I guess I need to save the Macro in a different Excel that will point to my data one.
Idealy would be to have one spreadsheet with my macro(let's call it macro.xls), that goes/read into the data excel(called Data.xls), copy everything into macro.xls , create me the new column and I just have to save it has a different name.
Hopefully m,y description is clear enought.
let me know if you need more info.

Thanks
David

Comment
Watch Question

David, Can you share a sample file so that I get my references correct?

Sid
Hi David:

Here what you need...

First regarding access overwriting the excel files.....well you need to write a macro in personal.xls and run it from there....so basically it serves as template....you still have the code anytime you create a new file.....following are the instructions on how to create Personal.xls

http://office.microsoft.com/en-us/excel-help/deploy-your-excel-macros-from-a-central-file-HA001087296.aspx

Now calculating NetworkDays in all the worksheets

Assuming you are trying to find dates between column "B"(Date Received) & column "C" (Invoice Date)  and result of network days will be placed in column "D"

Place the following code in the personal.xls and when you are done exporting you can run this macro from excel or you can just directly reference from Access as well...(If you like to do this then let me know and I will send you code to paste in Access to run macro from excel)




Sub CalcNWDays()
 Dim wsSheet As Worksheet
 Dim mdate, ndate As Date
    For Each wsSheet In ActiveWorkbook.Worksheets
        For m = 1 To 65000
            mdate = Range("B" & m)
            ndate = Range("C" & m)
            If mdate = "" And ndate = "" Then
                Exit For
            End If
            Range("D" & m) = Application.Run("ATPVBAEN.XLA!Networkdays", mdate, ndate)
        Next
    Next
End Sub

Open in new window

Author

Commented:
Thanks for the prompt response.
Sid, attached is the sample excel. hitsdoshi1 , I am gonna try your code. I don't need to run the code from Access.
I haven't look at the code yet, but if I understand correctly I need to save your code in a "template" excel and then open this template that will point to my new exported excel file? is that correct or do I have to copy it in the new exported excel everytime?
I will try it now thanks
Sample-File.xls
No, its works like template, when you open excel, code is always there.....so you don't need to copy or run it from other file. Just save the code in Personal.xls and after exporting your data just press F8 and you will see the macro CalNWDays

Author

Commented:
ok , I guess I did something wrong.
I have Exel 2010 on my machine , but the machine that will run it is an excel 2007. So I opened a new excel I clicked F11 to run the VB , I copy and paste your code , I saved it as Personal.xlsm ( excel told me that I have to save it as macro otherwise the macro will not be saved) , then I close everything. put the personal.xlsm and my raw data "raw.xls" ( which is a excel 97-2003 file) on the desktop.
open my Raw.xls go to view>macro and then I choose the macro (Personal.xlsm!ThisWorkbook.CalcNWDays) and on the bottom where it says macros in I choose my Excel file ( rawdata.xls and click Run
then I get a type mismatch error.
What am I  missing?

Thanks
David
David,  I can give you the code but there is a slight confusion. In your original post you mentioned that there are only 3 columns.

>>>First column is the Job# , the second is the Date Received, and the third is the Invoice Date.

But there are more than 3 columns and the Network days formula is referring to "Approved Date" in your file. Could you please clarify?

Sid
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
ok sorry for the confusion. I just the query again and I am attaching the raw output from my access database , so it is exactly how I will get the file.and I do see it's not consistent with the column order. maybe if it's easier , I can have one macro running for each individual  sheet , if you show me how I can run for sheet 1 and sheet 4 I can modify the code to replicate for the rest of the sheet.
thanks  and sorry for the confusion again. but the attachment is really the output one.
RawData.xls
>>>I do see it's not consistent with the column order. maybe if it's easier , I can have one macro running for each individual  sheet ,

No that is ok :) The code which I will give you will work for inconsistent columns as well. Please give me a short while :)

Sid
Quick question.

How are you generating the column headers? via query or manually?

Sid

Author

Commented:
thru the query
ok.

Sid
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
I forgot to delete line 89 and 90 from the above code. Please delete that as I was using it for testing purpose only.

Sid

Author

Commented:
I am gonna try it now. I will let you know .
Thanks

Author

Commented:
Wow , it's awesome. it works perfectly .
Thank you so much. I actually have a part 2 of this problem.
I am gonna open a new question link to this one. just to give you the heads up , I would like to calculate the mode and median of this new column and placed at the bottom of this column, also I have another excell, where I record all the median and mode for all those sheets.
I am opening the question now , so yo can see a better explanation.
thanks again, great job
Thanks. Glad to be of help :)

Sid

Author

Commented:
I just posted my new questions.
thanks
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.