Solved

Access VBA to normalize data in Excel

Posted on 2011-03-01
9
1,559 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DSum for Access 6 44
Unrecognized Database Format 8 91
MS accesss using VBA to change a default value 5 28
Alter an update query which rounds 7 31
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

912 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

25 Experts available now in Live!

Get 1:1 Help Now