taverny
asked on
Excel Macro
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
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
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)
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 SubASKER
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
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
ASKER
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!ThisWorkboo
then I get a type mismatch error.
What am I missing?
Thanks
David
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!ThisWorkboo
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
>>>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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
How are you generating the column headers? via query or manually?
Sid
ASKER
thru the query
ok.
Sid
Sid
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Sid
ASKER
I am gonna try it now. I will let you know .
Thanks
Thanks
ASKER
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
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
Sid
ASKER
I just posted my new questions.
thanks
thanks
Sid