Solved

Access VBA to normalize data in Excel

Posted on 2011-03-01
9
1,562 Views
Last Modified: 2012-06-27
I have the following macro in Excel (see normalized code in the attached files section) I would like to know how I can run this from Access.

Below is the code I have so far in between the start and end is where I am stuck

Private Sub cmdImport_Click()

On Error GoTo Err_cmdImport_Click

DoCmd.Echo False
DoCmd.SetWarnings False

Dim strMsg As String, strTitle As String
Dim IntStyle As Integer
Dim strMasterFileName As String
Dim strFileName As String
Dim strChoice As String


'Build the master import file name
strMasterFileName = CurrentProject.Path & "\Forecast Master Import.xls"

'Check if Master file exists
   If Dir(strMasterFileName) = "" Then
   strMsg = "The master file can not be located. Please load the Detailed Project Master.xlsm file in " & CurrentProject.Path
        strTitle = "Master File Required"
        IntStyle = vbOKOnly
        MsgBox strMsg, IntStyle, strTitle
        'Cancel = True
        GoTo ExitNow
    End If

    'Open the Excel file
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.Open (strMasterFileName)
    xlApp.Visible = True


'start
   
'I would like what the macro is doing and code here to run and normalize the data

'end

xlApp.Application.DisplayAlerts = False
xlApp.ActiveWorkbook.SaveAs FileName:=strFileName, CreateBackup:=False
xlApp.Application.DisplayAlerts = True

'Build the import table
strTableName = "00-NormalizedData"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTableName, strMasterFileName, True, "Normalized!"

Exit_cmdImport_Click:
    DoCmd.Echo True
    DoCmd.SetWarnings True
    Exit Sub

Err_cmdImport_Click:
    MsgBox Err.Description
    DoCmd.Echo True
    DoCmd.SetWarnings True
    Resume Exit_cmdImport_Click

ExitNow:
End Sub
Sub Normalize()
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Normalized").Delete
    Application.DisplayAlerts = True
    
    On Error GoTo 0
    
    ActiveWorkbook.Sheets("forecast.excel").Activate
    
    Dim r As Long, c As Long, LastR As Long, LastC As Long, arr As Variant, DestR As Long

    With ActiveSheet
        LastR = .Cells(.Rows.Count, 1).End(xlUp).Row
        LastC = .Cells(1, .Columns.Count).End(xlToLeft).Column
        arr = .Range(.[a1], .Cells(LastR, LastC)).Value
    End With
     
    'Worksheets.Add
    Worksheets.Add(After:=Worksheets(1)).Name = "Normalized"
    [a1:g1] = Array("Part_ID", "Desc", "Rev", "Supplier_Part_ID", "UOM", "Want_Date", "Qty")
    DestR = 1
    
    For r = 2 To LastR
        For c = 6 To LastC
            DestR = DestR + 1
            Range(Cells(DestR, 1), Cells(DestR, 7)) = Array(arr(r, 1), arr(r, 2), arr(r, 3), arr(r, 4), arr(r, 5), arr(1, c), arr(r, c))
        Next
    Next
    
    Columns.AutoFit
    
End Sub

Open in new window

0
Comment
Question by:tprocket
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:als315
ID: 35014592
Can you upload source and result in excel?
0
 

Author Comment

by:tprocket
ID: 35016615
Here is the Excel file. The access VBA is include in my original post
Forecast-Master-Import.xls
0
 
LVL 39

Expert Comment

by:als315
ID: 35017543
Do you like to have result in Access table or in Excel?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:tprocket
ID: 35017832
I want to run the code from access which will normalize the data in the excel spreadsheet and then import the excel data into an access table.
0
 
LVL 39

Expert Comment

by:als315
ID: 35017877
Can you convert xls to csv? It is preferrable way for access - to work with text file.
First 5 columns will be always the same? How many date columns can be?
0
 

Author Comment

by:tprocket
ID: 35018347
I have a delimited txt file available see attached.

Yes the first five columns will always be the same, the date columns can change.
Part Number|Revision|Description|Supplier Description|UOM|03/21/11|03/28/11|04/04/11|04/11/11|04/18/11|04/25/11|05/02/11|05/09/11|05/16/11|May-2011|Jun-2011|Jul-2011|Aug-2011|Sep-2011|Oct-2011|Nov-2011|Dec-2011|Jan-2012|Feb-2012|Mar-2012|Apr-2012|May-2012|Jun-2012|Jul-2012|Total
VBGT|000| TUBE||EA|0|0|58|0|60|0|44|0|64|0|88|100|84|76|116|124|72|132|32|0|0|0|0|0|1050
NUHTT|000|PLATE||EA|0|0|66|0|0|0|75|0|0|73|0|136|0|76|78|144|0|80|82|0|0|0|0|0|810

Open in new window

0
 
LVL 39

Expert Comment

by:als315
ID: 35018459
Text is better. But What you like to get for this columns:
05/16/11 - it is clear - date
May-2011 - ?
Do you need column Total?
This should be saved as text or as date?
0
 

Author Comment

by:tprocket
ID: 35018765
1. May-2011 should equal 5/1/2011 save as date
2. Total column not required
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 35021068
Check this sample
DB.zip
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now