#1
|
|||
|
|||
Excel VBA Macros
Hey guys. I am having trouble understanding requirements.
I would appreciate any and all productive guidance feedback, thank you in advance. So basically these are the excel VBA Macro requirements. 1. Macro mcrImport to import eight state/territory workbooks each into their own separate sheets in template National. Your macro should accomplish the eight imports using looping. The macro will be attached to the button with caption Import on the Summary sheet. 2. Macro mcrCombine to combine the eight new sheets into one new sheet called Combined. The data should begin from row 2 in sheet Combined to leave room for column headings. After combining the separate eight sheets, the macro should delete the eight separate state/territory sheets. The macro will be attached to the button with caption Combine on the Summary sheet. 3. Macro mcrValidate to perform validate and summarise tasks according to the details below. The macro will be attached to the button with caption Validate on the Summary sheet |
#2
|
||||
|
||||
Is this some kind of homework?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
||||
|
||||
Sure looks like it to me...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
No guys it's not any type of home work except the real kind of home work. The kind that earns you money.
I work in Web development for a small removal company. The only problem is. I am the only guy who is required to do all the I.T stuff. I agreed to look into it over the weekend and that is what I am doing. I have the templates. I discussed with the manager about my options and I told her I would be able to find a solution. |
#5
|
||||
|
||||
Your 'project description' seems to presuppose the existence of a userform, or at least worksheets with ActiveX controls, and a set of workbooks, one of which is called 'National', with a Summary sheet, a Combined sheet, plus eight state/territory workbooks. Without detailed knowledge of the layouts of the userform (including its command buttons, etc.) or ActiveX controls and these various workbooks & worksheets, it would be impossible for anyone to provide any meaningful code.
So, before anyone could make a start on this, you will need to provide at least the empty 'National' workbook, including its userform/ActiveX controls and the Summary & Combined sheets, plus at least one State workbook with some sample data. You can attach workbooks to a post via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen. I also can't see why anyone would specify that particular macro names (mcrImport, mcrCombine, mcrValidate) be used or that mcrImport "should accomplish the eight imports using looping", when nothing you've posted indicates why looping would be needed... As for wanting a macro "to perform validate and summarise tasks according to the details below", given that you haven't provided any of the said details, that one's going nowhere.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
Thank you for your feedback. I have attached screen shots of all relevant documentation. If required I can upload the documents.
List of documents I was given http://postimg.org/image/7t7ooq0lx/ Excel Table for VBA coding http://postimg.org/image/950qnedq5/ Word Doc with bookmarks to import data from http://postimg.org/image/3pft6pd6t/ Word Doc VBA side http://postimg.org/image/p9srvckn1/ I hope this helps you get a better understanding. If you require the documents I can upload them. |
#7
|
|||
|
|||
Quote:
I hope they uploaded properly. Last edited by macropod; 07-21-2015 at 05:52 AM. Reason: Data workbook containing DOB info, etc. deleted for privacy reasons |
#8
|
||||
|
||||
Try the code below. I've implemented the mcrImport & mcrCombine macros. The macro assumes the National & State files are all kept in the same folder.
You still haven't provided anything relevant to the mcrValidate macro and your WelcomeAboard template, though, would be better implemented as a mailmerge main document, for which no macros would be required. Code:
Option Explicit Dim StrSrc As String, StrState As String, xlWkBk As Workbook, xlWkSht As Worksheet Dim i As Long, lRowSrc As Long, lColSrc As Long, lRowTgt As Long Sub mcrImport() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim ArrState As Variant ArrState = Array("NSW", "Vic", "Qld", "WA", "SA", "Tas", "ACT", "NT") For i = 0 To UBound(ArrState) StrState = ArrState(i) StrSrc = ActiveWorkbook.Path & "\" & StrState & ".xls" If Dir(StrSrc) <> "" Then Call AddSheet(StrState, StrSrc) Next Application.CutCopyMode = False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Sub AddSheet(StrState As String, StrSrc As String) With ActiveWorkbook Set xlWkSht = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count)) With xlWkSht .Name = StrState 'Populate the header row .Range("A1").Value = "Family Name" .Range("B1").Value = "Given Name" .Range("C1").Value = "Gender" .Range("D1").Value = "D.O.B." .Range("E1").Value = "Address" .Range("F1").Value = "Suburb" .Range("G1").Value = "State" .Range("H1").Value = "Post Code" .Range("I1").Value = "Role" Call GetData(StrSrc) .Paste Destination:=.Cells(2, 1) End With End With Set xlWkSht = Nothing End Sub Sub GetData(StrSrc As String) Set xlWkBk = Workbooks.Open(Filename:=StrSrc, ReadOnly:=True, AddToMRU:=False) With xlWkBk With .Worksheets(1).UsedRange lRowSrc = .Cells.SpecialCells(xlCellTypeLastCell).Row lColSrc = .Cells.SpecialCells(xlCellTypeLastCell).Column .Range("A1", .Cells(lRowSrc, lColSrc)).Copy End With .Close 0 End With Set xlWkBk = Nothing End Sub Sub mcrCombine() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim ArrState As Variant ArrState = Array("NSW", "Vic", "Qld", "WA", "SA", "Tas", "ACT", "NT") With ActiveWorkbook Set xlWkSht = .Worksheets.Add(After:=.Worksheets(1)) xlWkSht.Name = "Combined" With xlWkSht 'Populate the header row .Range("A1").Value = "Family Name" .Range("B1").Value = "Given Name" .Range("C1").Value = "Gender" .Range("D1").Value = "D.O.B." .Range("E1").Value = "Address" .Range("F1").Value = "Suburb" .Range("G1").Value = "State" .Range("H1").Value = "Post Code" .Range("I1").Value = "Role" End With For i = 0 To UBound(ArrState) lRowTgt = lRowTgt + 1 StrState = ArrState(i) If SheetExists(StrState) = True Then Call ImportSheet(StrState) .Worksheets(StrState).Delete End If Next End With Set xlWkSht = Nothing Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Sub ImportSheet(StrSrc As String) With ActiveWorkbook With .Worksheets(StrSrc).UsedRange lRowSrc = .Cells.SpecialCells(xlCellTypeLastCell).Row lColSrc = .Cells.SpecialCells(xlCellTypeLastCell).Column .Range("A2", .Cells(lRowSrc, lColSrc)).Copy End With With .Worksheets("Combined") lRowTgt = .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row + 1 .Paste Destination:=.Cells(lRowTgt, 1) End With End With End Sub Function SheetExists(SheetName As String) As Boolean ' returns TRUE if the sheet exists in the active workbook SheetExists = False On Error GoTo NoSuchSheet If Len(Sheets(SheetName).Name) > 0 Then SheetExists = True Exit Function End If NoSuchSheet: End Function
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
Thank you.
I will attach Doc Template now with Macro Requirements. If you require any of the other documents, just let me know. Word macros mcrOK: to populate the bookmarks within the letter text in the body of the template. The macro will be attached to the UserForm button with caption OK. mcrLog: to append person data to the text file Latelog.txt and hide the UserForm. The macro will be attached to the UserForm button with caption Log. mcrClear: to clear the UserForm fields. The macro will be attached to the UserForm button with caption Clear. mcrClose: to close the UserForm. The macro will be attached to the UserForm button with caption Close. |
#10
|
||||
|
||||
I do have to wonder whether you paid any attention to what I said about your WelcomeAboard template and which you quoted in your own reply:
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
Quote:
Its midnight at the moment for me so I'll have to review it in the morning. |
#12
|
|||
|
|||
Quote:
So in regards to the mcrValidate. I was given the following information. In row 1 of sheet Combined the following column headings: Last name, First name, Gender, DOB, Address, Suburb, State, Postcode, Role, Category. Standardise all fonts to plain Arial 12 pt. The column Category in sheet Combined was not in the original states data. Its purpose is to record the age category for each member. The various categories from Junior to Great Grand Master Junior - Under 18 Senior - 18+ Master - 40+ Grand Master - 50+ Great Grand Master - 60+ For each member data, generate the age category for column Category. for each row subtract DOB from your Nationals date to find the member's expected age on the day of competition, then compare with the category table below. Write out the category data for the member in full. I hope this helps. You have already helped immensely and I do appreciate it. |
#13
|
||||
|
||||
Quote:
That said, your WelcomeAboard template has a quite unnecessary userform and nothing to indicate whether or how the fields in it should be filled. I say unnecessary because, even with a macro, everything could be done in the body of the document, using content controls. Presumably the data are meant to come from the workbook, but there's nothing to indicate how any given record from the hundreds (or more) in the workbook might be used to populate the userform. Do the people behind this imagine the userform is going to fill itself out programmatically, without human intervention? Quote:
Finally, the Category info isn't provided for in the WelcomeAboard template, so I'm not sure what the point of those calculations is. Updated workbook attached.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#14
|
|||
|
|||
I understand what you are saying. I would be happy to pay for your services. If you would like to send me a quote to my email address.
Last edited by macropod; 07-20-2015 at 10:11 PM. Reason: email address removed for privacy |
#15
|
||||
|
||||
Sorry, but I don't do paid work. In any event, it's a trivial project. Someone closer to home for you, who may be interested, is another MS MVP, Doug Robbins - http://au.linkedin.com/pub/doug-robbins/11/139/318
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel 2000 macros in excel 13 | terryg | Excel | 1 | 03-30-2015 05:18 AM |
Excel 97 macros no longer run | PeteNC | Excel | 3 | 12-07-2014 06:22 PM |
Run macros between excel and access | Jhouk5244 | Office | 1 | 08-18-2011 11:06 AM |
macros to excel | evh | Excel Programming | 0 | 07-20-2011 10:12 PM |