• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

Excel 2010 macro

Hi,
is it possible to create an excel macro to create the template file per the attachments?
The template file and raw data file are in the same directory. The raw data file's information must be copied in to the Template file per criteria.
Also I need the template to be saved as template_datetimestamp.xls in the same directory.
Template.xlsx
Rawdata.xlsx
0
kris_sk2012
Asked:
kris_sk2012
  • 6
  • 6
  • 2
2 Solutions
 
Robert SchuttSoftware EngineerCommented:
I think the attached workbook contains everything you need.
Template.xlsm
0
 
krishnakrkcCommented:
HI

try. This code goes in the RawData workbook.

Option Explicit

Sub kTest()
    
    Dim wbkActive       As Workbook
    Dim wbkTemplate     As Workbook
    Dim dicProID        As Object
    Dim i   As Long, x
    Dim strItems        As String
    Dim Basic, Process
    
    
    Set wbkActive = ThisWorkbook
    Set wbkTemplate = Workbooks.Open(wbkActive.Path & "\Template.xlsx")
        
    With wbkActive.Worksheets("Basic").Range("a1").CurrentRegion
        Basic = .Resize(, .Columns.Count + 2).Value2
    End With
    
    Process = wbkActive.Worksheets("Process").Range("a1").CurrentRegion
    
    Set dicProID = CreateObject("scripting.dictionary")
        dicProID.comparemode = 1
    
    For i = 2 To UBound(Basic, 1)
        If Len(Basic(i, 1)) Then
            dicProID.Item(Basic(i, 1)) = Array(i, vbNullString)
        End If
    Next
    
    For i = 2 To UBound(Process, 1)
        If dicProID.exists(Process(i, 1)) Then
            x = dicProID.Item(Process(i, 1))
            strItems = Basic(x(0), 8)
            If Len(strItems) Then
                Basic(x(0), 7) = Process(i, 5)
                Basic(x(0), 8) = Basic(x(0), 8) & vbLf & Process(i, 6) & " - " & Process(i, 7)
                dicProID.Item(Process(i, 1)) = Array(x(0), Basic(x(0), 8))
            Else
                Basic(x(0), 7) = Process(i, 5)
                Basic(x(0), 8) = Process(i, 6) & " - " & Process(i, 7)
                dicProID.Item(Process(i, 1)) = Array(x(0), Basic(x(0), 8))
            End If
        End If
    Next
    
    If dicProID.Count Then
        With wbkTemplate.Worksheets(1)
            .UsedRange.ClearContents
            .Range("a1").Resize(UBound(Basic, 1), UBound(Basic, 2)) = Basic
            .Range("g1:h1") = [{"dori","procid - procn"}]
        End With
        wbkTemplate.SaveAs Left$(wbkTemplate.Name, InStrRev(wbkTemplate.Name, ".") - 1) & "_" & Format(Now, "mmddyy_hhmm"), 51 '51 - Default, 56 - .xls
        wbkTemplate.Close 0
    End If
    
End Sub

Open in new window


Kris
0
 
kris_sk2012Author Commented:
The raw data file is a auto generated file. Can the above code be executed from the template file which I have more control on?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
krishnakrkcCommented:
Hi

Here you go.

Option Explicit

Sub kTest()
    
    Dim wbkTemplate     As Workbook
    Dim wbkRawData      As Workbook
    Dim dicProID        As Object
    Dim i   As Long, x
    Dim strItems        As String
    Dim Basic, Process
    
    
    Set wbkTemplate = ThisWorkbook
    Set wbkRawData = Workbooks.Open(wbkTemplate.Path & "\RawData.xlsx")
        
    With wbkRawData.Worksheets("Basic").Range("a1").CurrentRegion
        Basic = .Resize(, .Columns.Count + 2).Value2
    End With
    
    Process = wbkRawData.Worksheets("Process").Range("a1").CurrentRegion
    
    Set dicProID = CreateObject("scripting.dictionary")
        dicProID.comparemode = 1
    
    For i = 2 To UBound(Basic, 1)
        If Len(Basic(i, 1)) Then
            dicProID.Item(Basic(i, 1)) = Array(i, vbNullString)
        End If
    Next
    
    For i = 2 To UBound(Process, 1)
        If dicProID.exists(Process(i, 1)) Then
            x = dicProID.Item(Process(i, 1))
            strItems = Basic(x(0), 8)
            If Len(strItems) Then
                Basic(x(0), 7) = Process(i, 5)
                Basic(x(0), 8) = Basic(x(0), 8) & vbLf & Process(i, 6) & " - " & Process(i, 7)
                dicProID.Item(Process(i, 1)) = Array(x(0), Basic(x(0), 8))
            Else
                Basic(x(0), 7) = Process(i, 5)
                Basic(x(0), 8) = Process(i, 6) & " - " & Process(i, 7)
                dicProID.Item(Process(i, 1)) = Array(x(0), Basic(x(0), 8))
            End If
        End If
    Next
    
    If dicProID.Count Then
        With wbkTemplate.Worksheets(1)
            .UsedRange.ClearContents
            .Range("a1").Resize(UBound(Basic, 1), UBound(Basic, 2)) = Basic
            .Range("g1:h1") = [{"dori","procid - procn"}]
        End With
        wbkTemplate.Worksheets(1).Copy
        ActiveWorkbook.SaveAs wbkTemplate.Path & "\" & Left$(wbkTemplate.Name, InStrRev(wbkTemplate.Name, ".") - 1) & "_" & Format(Now, "mmddyy_hhmm"), 51 '51 - Default, 56 - .xls
        ActiveWorkbook.Close 0
        wbkRawData.Close 0
        Set wbkRawData = Nothing
    End If
    
End Sub

Open in new window


Kris
0
 
kris_sk2012Author Commented:
@ robert_schutt
if I have three static header rows in the template worksheet, what part of the code should be modified so that the data in populated starting at the 4th row?
I'm using your code to test.
0
 
kris_sk2012Author Commented:
@ robert_schutt
I can submit a new question and close this out, but wanted to see if you can suggest anything if I have another tab in the raw data file.
The template has been updated to use the Doc tab info from the raw data file in to the template file. So far, your program is working as expected.
Rawdata.xlsx
Template.xlsx
0
 
Robert SchuttSoftware EngineerCommented:
ok, the first question should be easy to fix, where you see "For rw = 2 to ..." you can change the 2 to 4 to start at the 4th row. The second question I'll need a bit of time but I'll have a look, no need to start a new question yet, maybe it's a small change. Please make sure you have a good look at any proposed solution, at the moment it's not clear to me if you favor either of the solutions, whether they both do what you needed etc...?
0
 
Robert SchuttSoftware EngineerCommented:
ok, it's possible to add another lookup table, I've done that in this new file. As far as I can tell from looking at the code posted by @krishnakrkc that should also be easy to add there if you like the structure of that code better (may very well be, I like it myself ;-) it looks clean).
Template.xlsm
0
 
kris_sk2012Author Commented:
@ robert_schutt
I made minor changes to the raw file and template files (column inserted) and the script is not working as expected. I tried to tweak the row numbers, but it's failing. Also I've included the static headers, when I change rw from 2 to 4, the script is skipping first two rows of raw data tab information.
I'm so sorry that I'm dragging this.
Rawdata.xlsx
Template.xlsx
0
 
Robert SchuttSoftware EngineerCommented:
Don't feel bad. I'll gladly have another look.

I think the best would be to add 2 constants to the code so that future changes can be handled easily.

Actually made that change but got stuck while testing because a key column in the Doc tab was deleted and another column added. You'll need to explain what is the correct format or if column headers need to be checked or something like that.
0
 
kris_sk2012Author Commented:
Hi - when I try to open the saved workbook with date/time stamp, the macro is also copied over from the template. How can I remove the macro within the code.
I'm using workbook_open, so I really need the macro to be disabled in the saved workbook, otherwise, it just keeps kicking off every time I open the output file.
0
 
Robert SchuttSoftware EngineerCommented:
Maybe it's easier to create a new workbook in the code. What you suggest is possible, but 'dangerous' see here: http://www.cpearson.com/excel/vbe.aspx
0
 
Robert SchuttSoftware EngineerCommented:
Well, I made some assumptions about the changed Doc sheet and came up with this new version.
Template.xlsm
0
 
kris_sk2012Author Commented:
Thanks Robert.

I've also have an extension to the above question - if another worksheet needs data to be populated from the raw data file, how can that be done. Question is posted as new and below is the URL.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27875344.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now