Solved

Access VBA to normalize data in Excel

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

 

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

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…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

828 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