Access VBA to normalize data in Excel

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

tprocketAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
als315Connect With a Mentor Commented:
Check this sample
DB.zip
0
 
als315Commented:
Can you upload source and result in excel?
0
 
tprocketAuthor Commented:
Here is the Excel file. The access VBA is include in my original post
Forecast-Master-Import.xls
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
als315Commented:
Do you like to have result in Access table or in Excel?
0
 
tprocketAuthor Commented:
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
 
als315Commented:
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
 
tprocketAuthor Commented:
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
 
als315Commented:
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
 
tprocketAuthor Commented:
1. May-2011 should equal 5/1/2011 save as date
2. Total column not required
0
All Courses

From novice to tech pro — start learning today.