Solved

Access VBA to normalize data in Excel

Posted on 2011-03-01
9
1,589 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 40

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 40

Expert Comment

by:als315
ID: 35017543
Do you like to have result in Access table or in Excel?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 40

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 40

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 40

Accepted Solution

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…

717 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