tprocket
asked on
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.Applic ation")
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.DisplayA lerts = False
xlApp.ActiveWorkbook.SaveA s FileName:=strFileName, CreateBackup:=False
xlApp.Application.DisplayA lerts = 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
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.Applic
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.DisplayA
xlApp.ActiveWorkbook.SaveA
xlApp.Application.DisplayA
'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
Can you upload source and result in excel?
ASKER
Here is the Excel file. The access VBA is include in my original post
Forecast-Master-Import.xls
Forecast-Master-Import.xls
Do you like to have result in Access table or in Excel?
ASKER
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.
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?
First 5 columns will be always the same? How many date columns can be?
ASKER
I have a delimited txt file available see attached.
Yes the first five columns will always be the same, the date columns can change.
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
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?
05/16/11 - it is clear - date
May-2011 - ?
Do you need column Total?
This should be saved as text or as date?
ASKER
1. May-2011 should equal 5/1/2011 save as date
2. Total column not required
2. Total column not required
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.