Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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