Re: $$Excel-Macros$$ Sum downtime for equipment with overlap periods
Dear Ganesh Thank for your advise but i found out there are some mistake in your formula.Please you refer enclosed file and I hope you might give new solution. Son 2014-11-26 13:26 GMT+07:00 Ganesh N ganeshg...@gmail.com: Dear son nguyen, PFA Regards, Ganesh N On Wed, Nov 26, 2014 at 10:03 AM, Ganesh N ganeshg...@gmail.com wrote: Dear son nguyen, Before putting the my formula just have sort in start time. Regards, Ganesh N On Wed, Nov 26, 2014 at 10:01 AM, Ganesh N ganeshg...@gmail.com wrote: Dear son nguyen, Try this formula... Let me know if you have any concern. Thanks Regards, Ganesh N On Tue, Nov 25, 2014 at 7:07 PM, Paul Schreiner schreiner_p...@att.net wrote: Can you send me a sample file? Otherwise, I'll probably spend far too much time trying to recreate something that doesn't apply to your situation. *Paul* - *“Do all the good you can,By all the means you can,In all the ways you can,In all the places you can,At all the times you can,To all the people you can,As long as ever you can.” - John Wesley* - *From:* son nguyen sonnguye...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Monday, November 24, 2014 11:22 PM *Subject:* Re: $$Excel-Macros$$ Sum downtime for equipment with overlap periods Mr Paul, Thank for your consider.Let me explain more : There are lots of parts within machine so i would like to list detail of their working stop time ,however when calculating downtime i only calculate actual stop time and eliminate overlap time. Son 2014-11-24 20:47 GMT+07:00 Paul Schreiner schreiner_p...@att.net: What do these times represent? Why would the equipment be recorded as down 9:00-12:00 AND 10:00-11:30? Is there a minimum recording increment? as in: 5 minute minimum or is it (1) minute increments? I'm considering setting up an array of increments for 24 hours and flagging the increment to avoid duplications. But would prefer NOT to have 1440 increments (24 hrs * 60min/hr) *Paul* - *“Do all the good you can,By all the means you can,In all the ways you can,In all the places you can,At all the times you can,To all the people you can,As long as ever you can.” - John Wesley* - *From:* son nguyen sonnguye...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Saturday, November 22, 2014 11:55 PM *Subject:* $$Excel-Macros$$ Sum downtime for equipment with overlap periods Dear all, I have problem when calculate total equipment downtime as there are overlap periods. For example : start time Finish time 8:00 8:20 9:00 12:00 10:00 11:30 ... start time,finish time are flexible. all of you might give me formula to total hours but exclude overlap. -- Best Regards Son -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and
$$Excel-Macros$$ Shortcut required for this format !!
[h]:mm:ss What is the keyboard shortcut to set the time in this format. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Shortcut required for this format !!
Sorry, in excel there is no shortcut related specify format... you can use ctrl+shift+@ for general time format . Enjoy Team XLS On Wed, Nov 26, 2014 at 3:29 PM, Mandeep Baluja rockerna...@gmail.com wrote: [h]:mm:ss What is the keyboard shortcut to set the time in this format. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Shortcut required for this format !!
build this hope this help someone else. Sub Format_to_time() Selection.NumberFormat = [h]:mm:ss Selection.Font.Bold = True End Sub On Wednesday, November 26, 2014 3:29:40 PM UTC+5:30, Mandeep Baluja wrote: [h]:mm:ss What is the keyboard shortcut to set the time in this format. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Shortcut required for this format !!
is this your question for help...do you want to check our knowledge? . Enjoy Team XLS On Wed, Nov 26, 2014 at 4:02 PM, Mandeep Baluja rockerna...@gmail.com wrote: build this hope this help someone else. Sub Format_to_time() Selection.NumberFormat = [h]:mm:ss Selection.Font.Bold = True End Sub On Wednesday, November 26, 2014 3:29:40 PM UTC+5:30, Mandeep Baluja wrote: [h]:mm:ss What is the keyboard shortcut to set the time in this format. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Shortcut required for this format !!
No I don't want to check you knowledge. I had given the solution to share knowledge. As far your team is concerned you are far better than me. On Wednesday, November 26, 2014 4:07:51 PM UTC+5:30, XLS S wrote: is this your question for help...do you want to check our knowledge? . Enjoy Team XLS On Wed, Nov 26, 2014 at 4:02 PM, Mandeep Baluja rocke...@gmail.com javascript: wrote: build this hope this help someone else. Sub Format_to_time() Selection.NumberFormat = [h]:mm:ss Selection.Font.Bold = True End Sub On Wednesday, November 26, 2014 3:29:40 PM UTC+5:30, Mandeep Baluja wrote: [h]:mm:ss What is the keyboard shortcut to set the time in this format. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros...@googlegroups.com javascript:. To post to this group, send email to excel-...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Macro help needed for converting all the xls files into csv placing the csv files
Good Morning, Hope you are having a great day! *Objective: I want to convert all the excel files (.xls,.xlsx etc) in a folder to .csv files (with original file names) place the csv file in another target folder.* I am not an excel expert, however I managed to write the below piece of code. It converts a single xl file to csv but places it in my desktop. Please help! Public Sub xls2csv() Dim varArrFile As Variant Dim intCtr As Integer Dim strFilesAs String Dim wb As Workbook varArrFile = Application.GetOpenFilename(*.xls,*.xlsx, , Select file(s) to convert, , True) If IsArray(varArrFile) = False Then MsgBox No file is selected, select a file to continue Exit Sub Else For intCtr = LBound(varArrFile) To UBound(varArrFile) Set wb = Workbooks.Open(varArrFile(intCtr)) wb.SaveAs ThisWorkbook.Path \ intCtr .CSV, 51 wb.Close Set wb = Nothing Next MsgBox The files are successfully converted into .csv format placed in target folder End If End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Introduce Yourself !!
Hello buddies, This is Muneera Khan from Amsterdam, the Netherlands Currently working as Business Process Analyst for Market Risk Management at ING Bank. I love museums, travelling, painting, arts fashion! I highly appreciate all the contributers here providing very good supportAnd I am here to grasp as much as knowledge I can from all the excel gurus out there. Fijne Dag! -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Shortcut required for this format !!
Dear, we are not concerned about it. Thank u for your solution . Enjoy Team XLS On Wed, Nov 26, 2014 at 4:16 PM, Mandeep Baluja rockerna...@gmail.com wrote: No I don't want to check you knowledge. I had given the solution to share knowledge. As far your team is concerned you are far better than me. On Wednesday, November 26, 2014 4:07:51 PM UTC+5:30, XLS S wrote: is this your question for help...do you want to check our knowledge? . Enjoy Team XLS On Wed, Nov 26, 2014 at 4:02 PM, Mandeep Baluja rocke...@gmail.com wrote: build this hope this help someone else. Sub Format_to_time() Selection.NumberFormat = [h]:mm:ss Selection.Font.Bold = True End Sub On Wednesday, November 26, 2014 3:29:40 PM UTC+5:30, Mandeep Baluja wrote: [h]:mm:ss What is the keyboard shortcut to set the time in this format. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros...@googlegroups.com. To post to this group, send email to excel-...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Macro help needed for converting all the xls files into csv placing the csv files
Sub CSV_to_XLS() Dim wb As Workbook Dim strFile As String, strDir As String SourcePath = C:\Users\pc\Desktop\45.November.2014\ strFile = Dir(SourcePath *.xlsx) Do While strFile Set wb = Workbooks.Open(Filename:=SourcePath strFile, Local:=True) newpath = C:\Users\pc\Desktop\a\ k = Replace(wb.FullName, .xlsx, .csv) q = Replace(k, SourcePath, newpath) wb.SaveAs q wb.Close True Set wb = Nothing strFile = Dir Loop End Sub Set source path and new path according to your convenience it will convert xlsx files to csv in new path folder. Regards, Mandeep Baluja Excel Specialist. https://www.linkedin.com/profile/view?id=312532939 https://www.facebook.com/VBAEXCELSQL?ref=hl On Wednesday, November 26, 2014 4:40:03 PM UTC+5:30, Muneera Shabana wrote: Good Morning, Hope you are having a great day! *Objective: I want to convert all the excel files (.xls,.xlsx etc) in a folder to .csv files (with original file names) place the csv file in another target folder.* I am not an excel expert, however I managed to write the below piece of code. It converts a single xl file to csv but places it in my desktop. Please help! Public Sub xls2csv() Dim varArrFile As Variant Dim intCtr As Integer Dim strFilesAs String Dim wb As Workbook varArrFile = Application.GetOpenFilename(*.xls,*.xlsx, , Select file(s) to convert, , True) If IsArray(varArrFile) = False Then MsgBox No file is selected, select a file to continue Exit Sub Else For intCtr = LBound(varArrFile) To UBound(varArrFile) Set wb = Workbooks.Open(varArrFile(intCtr)) wb.SaveAs ThisWorkbook.Path \ intCtr .CSV, 51 wb.Close Set wb = Nothing Next MsgBox The files are successfully converted into .csv format placed in target folder End If End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Macro help needed for converting all the xls files into csv placing the csv files
Sub XLS_to_CSV() Dim wb As Workbook Dim strFile As String, strDir As String SourcePath = C:\Users\pc\Desktop\45.November.2014\ strFile = Dir(SourcePath *.xlsx) Do While strFile Set wb = Workbooks.Open(Filename:=SourcePath strFile, Local:=True) newpath = C:\Users\pc\Desktop\a\ k = Replace(wb.FullName, .xlsx, .csv) q = Replace(k, SourcePath, newpath) wb.SaveAs q wb.Close True Set wb = Nothing strFile = Dir Loop End Sub Regards, Mandeep Baluja Excel Specialist. https://www.linkedin.com/profile/view?id=312532939 https://www.facebook.com/VBAEXCELSQL?ref=hl On Wednesday, November 26, 2014 4:40:03 PM UTC+5:30, Muneera Shabana wrote: Good Morning, Hope you are having a great day! *Objective: I want to convert all the excel files (.xls,.xlsx etc) in a folder to .csv files (with original file names) place the csv file in another target folder.* I am not an excel expert, however I managed to write the below piece of code. It converts a single xl file to csv but places it in my desktop. Please help! Public Sub xls2csv() Dim varArrFile As Variant Dim intCtr As Integer Dim strFilesAs String Dim wb As Workbook varArrFile = Application.GetOpenFilename(*.xls,*.xlsx, , Select file(s) to convert, , True) If IsArray(varArrFile) = False Then MsgBox No file is selected, select a file to continue Exit Sub Else For intCtr = LBound(varArrFile) To UBound(varArrFile) Set wb = Workbooks.Open(varArrFile(intCtr)) wb.SaveAs ThisWorkbook.Path \ intCtr .CSV, 51 wb.Close Set wb = Nothing Next MsgBox The files are successfully converted into .csv format placed in target folder End If End Sub -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Introduce Yourself !!
hey welcome, keep sharing your knowledge! Fijne Avond! + *I did not do this for you. God is here working through me for you.* On Wed, Nov 26, 2014 at 4:47 PM, Muneera Shabana muneera.shab...@gmail.com wrote: Hello buddies, This is Muneera Khan from Amsterdam, the Netherlands Currently working as Business Process Analyst for Market Risk Management at ING Bank. I love museums, travelling, painting, arts fashion! I highly appreciate all the contributers here providing very good supportAnd I am here to grasp as much as knowledge I can from all the excel gurus out there. Fijne Dag! -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Sum downtime for equipment with overlap periods
Could you double-check your calculation again? in your sample, transction #6 (12:30-14:30) is completely contained within transaction #5 (12:00-16:00) so would not contribute to the total. my calculation comes up with 5:50 instead of 6:20 Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: son nguyen sonnguye...@gmail.com To: excel-macros@googlegroups.com Sent: Tuesday, November 25, 2014 11:21 PM Subject: Re: $$Excel-Macros$$ Sum downtime for equipment with overlap periods Hi Paul, I'm sorry as my past explain isn't clear for you.Please see enclosed file and hope that i'll receive excellent solution of all of you. Thanks a lot Son 2014-11-25 20:37 GMT+07:00 Paul Schreiner schreiner_p...@att.net: Can you send me a sample file? Otherwise, I'll probably spend far too much time trying to recreate something that doesn't apply to your situation. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: son nguyen sonnguye...@gmail.com To: excel-macros@googlegroups.com Sent: Monday, November 24, 2014 11:22 PM Subject: Re: $$Excel-Macros$$ Sum downtime for equipment with overlap periods Mr Paul, Thank for your consider.Let me explain more : There are lots of parts within machine so i would like to list detail of their working stop time ,however when calculating downtime i only calculate actual stop time and eliminate overlap time. Son 2014-11-24 20:47 GMT+07:00 Paul Schreiner schreiner_p...@att.net: What do these times represent? Why would the equipment be recorded as down 9:00-12:00 AND 10:00-11:30? Is there a minimum recording increment? as in: 5 minute minimum or is it (1) minute increments? I'm considering setting up an array of increments for 24 hours and flagging the increment to avoid duplications. But would prefer NOT to have 1440 increments (24 hrs * 60min/hr) Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: son nguyen sonnguye...@gmail.com To: excel-macros@googlegroups.com Sent: Saturday, November 22, 2014 11:55 PM Subject: $$Excel-Macros$$ Sum downtime for equipment with overlap periods Dear all, I have problem when calculate total equipment downtime as there are overlap periods. For example : start time Finish time 8:00 8:20 9:00 12:00 10:00 11:30 ... start time,finish time are flexible. all of you might give me formula to total hours but exclude overlap. -- Best Regards Son -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4)
$$Excel-Macros$$ VBA Functions and methods.
Where can I find a list of all methods and functions for VBA in Excel. I want it in readable format not while searching with the object explorer. various website is showing but not a complete information in single link. Regards, Mandeep Baluja Excel Specialist. https://www.linkedin.com/profile/view?id=312532939 https://www.facebook.com/VBAEXCELSQL?ref=hl -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Add and remove text box on ever time button click.
Hello Expert, No one has solution, that is ok, at least give me some hint so that I can do it my own. On Thu, Nov 20, 2014 at 7:26 PM, Paul Schreiner schreiner_p...@att.net wrote: OK, I'll take a look at this as soon as I get some things I'm PAID to do taken care of! *Paul* - *“Do all the good you can,By all the means you can,In all the ways you can,In all the places you can,At all the times you can,To all the people you can,As long as ever you can.” - John Wesley* - *From:* Rupesh Patil patil.rupesh1...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Wednesday, November 19, 2014 11:17 PM *Subject:* Re: $$Excel-Macros$$ Add and remove text box on ever time button click. Hello Paul, Thanks for reply Are you saying that when you hit Add you want to add another row of (5) textboxes? Yes, I want to add another row of (5) TextBoxes. And when you hit Remove, it should remove the row of (5) boxes? Yes, I want to remove row of (5) tesxtboxex. If you remove the second row, what do you want to have happen to the rest? (third row move up to row 2 or ??) Yes, third row move up to row 2. Is there a limit to the number of rows? Yes, for now limit should be 12 rows of text boxes what happens when you exceed the size of the userform? in that case user form increasable accordingly or scroll bar will work. do you want the macro to increase the size of the userform? I want macro for add textboxes on Add button click every time and remove added textboxes on Remove button click. Thanks again for reply, Please help On Wed, Nov 19, 2014 at 6:07 PM, Paul Schreiner schreiner_p...@att.net wrote: Your userform currently has (3) rows of(5) textboxes. Are you saying that when you hit Add you want to add another row of (5) textboxes? And when you hit Remove, it should remove the row of (5) boxes? If you remove the second row, what do you want to have happen to the rest? (third row move up to row 2 or ??) Is there a limit to the number of rows? what happens when you exceed the size of the userform? do you want the macro to increase the size of the userform? *Paul* - *“Do all the good you can,By all the means you can,In all the ways you can,In all the places you can,At all the times you can,To all the people you can,As long as ever you can.” - John Wesley* - *From:* Rupesh Patil patil.rupesh1...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Wednesday, November 19, 2014 3:24 AM *Subject:* Re: $$Excel-Macros$$ Add and remove text box on ever time button click. Please Experts help me out. On Tue, Nov 18, 2014 at 5:07 PM, Rupesh Patil patil.rupesh1...@gmail.com wrote: Hello Experts, any help? On Mon, Nov 17, 2014 at 4:10 PM, Rupesh Patil patil.rupesh1...@gmail.com wrote: Hello experts, I need your help,I have one userform which attached with mail for your reference, what I need is add text boxes vertical on button every time click Add Button and remove this text boxes every time on Remove Button click. and Add-Remove row accordingly in sheet. -- Thanks Regards, Rupesh Patil, (9970196189) . -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Thanks Regards, Rupesh Patil, (9970196189), Ayushman Pvt. Ltd. -- Thanks Regards, Rupesh Patil, (9970196189), Ayushman Pvt. Ltd. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please
$$Excel-Macros$$ Re: Add and remove text box on ever time button click.
why dont you hide buttons on click and unhide on another On Monday, November 17, 2014 4:10:26 PM UTC+5:30, Rupesh Patil wrote: Hello experts, I need your help,I have one userform which attached with mail for your reference, what I need is add text boxes vertical on button every time click Add Button and remove this text boxes every time on Remove Button click. and Add-Remove row accordingly in sheet. -- Thanks Regards, Rupesh Patil, (9970196189) . -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups MS EXCEL AND VBA MACROS group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.