Option Compare Database
Public Sub ImportData()
DoCmd.SetWarnings False
Call MB25
Call MB52Inventory
Call PKMC
Call WHtrans
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM MaSAT"
Call AX01_MASA_REPORT
Call AX02_MASA_REPORT
Call HX01_MASA_REPORT
Call JL01_MASA_REPORT
End Sub
Public Sub MB25()
Dim sPath As String, Src1 As String, Irc1 As String
sPath = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\"
Src1 = sPath & "MB25.XLSX"
Irc1 = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\MB25.XLSX"
Dim xlObj As Object
Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open Irc1
xlObj.DisplayAlerts = False
xlObj.ActiveWorkbook.SaveAs Src1, FileFormat:=51
xlObj.Quit
Set xlObj = Nothing
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "MB25T"
DoCmd.TransferSpreadsheet acImport, 10, "MB25T", Src1, True, ""
DoCmd.DeleteObject acTable, "Sheet1$_ImportErrors"
DoCmd.SetWarnings True
End Sub
Public Sub MB52Inventory()
Dim sPath As String, Src1 As String
sPath = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\"
Src1 = sPath & "MB52Inventory.XLSX"
Irc1 = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\MB52Inventory.XLSX"
Dim xlObj As Object
Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open Irc1
xlObj.DisplayAlerts = False
xlObj.ActiveWorkbook.SaveAs Src1, FileFormat:=51
xlObj.Quit
Set xlObj = Nothing
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "MB52InventoryT"
DoCmd.TransferSpreadsheet acImport, 10, "MB52InventoryT", Src1, True, ""
DoCmd.DeleteObject acTable, "Sheet1$_ImportErrors"
DoCmd.SetWarnings True
End Sub
Public Sub PKMC()
Dim sPath As String, Src1 As String
sPath = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\"
Src1 = sPath & "PKMC.XLSX"
Irc1 = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\PKMC.XLSX"
Dim xlObj As Object
Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open Irc1
xlObj.DisplayAlerts = False
xlObj.ActiveWorkbook.SaveAs Src1, FileFormat:=51
xlObj.Quit
Set xlObj = Nothing
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "PKMCT"
DoCmd.TransferSpreadsheet acImport, 10, "PKMCT", Src1, True, ""
'DoCmd.DeleteObject acTable, "Sheet1$_ImportErrors"
DoCmd.SetWarnings True
End Sub
Public Sub WHtrans()
Dim sPath As String, Src1 As String
sPath = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\"
Src1 = sPath & "WHtrans.xlsx"
Irc1 = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\WHtrans.xlsx"""
Dim xlObj As Object
Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open Irc1
xlObj.DisplayAlerts = False
xlObj.ActiveWorkbook.SaveAs Src1, FileFormat:=51
xlObj.Quit
Set xlObj = Nothing
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "WHtransT"
DoCmd.TransferSpreadsheet acImport, 10, "WHtransT", Src1, True, ""
'DoCmd.DeleteObject acTable, "Sheet1$_ImportErrors"
DoCmd.SetWarnings True
End Sub
Public Sub AX01_MASA_REPORT()
Dim sPath As String, Src1 As String, Src2 As String
sPath = "\\Frxfileserv1\FTPDOWN\WW_PUBLIC\Download Folder\New Product Programs-MASA\Local\AX01" & "\"
Src1 = sPath & "AX01_MASA_REPORT.XLS"
Src2 = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\AX01_MASA_REPORT.XLS"
Dim xlObj As Object
Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open Src1
xlObj.Visible = True
xlObj.DisplayAlerts = False
xlObj.ActiveWorkbook.SaveAs Src2, FileFormat:=51
xlObj.Quit
Set xlObj = Nothing
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, 10, "MaSAT", Src2, True, ""
DoCmd.DeleteObject acTable, "AX01_MASA_REPORT$_ImportErrors"
DoCmd.SetWarnings True
End Sub
Public Sub AX02_MASA_REPORT()
Dim sPath As String, Src1 As String, Src2 As String
sPath = "\\Frxfileserv1\FTPDOWN\WW_PUBLIC\Download Folder\New Product Programs-MASA\Local\AX02" & "\"
Src1 = sPath & "AX02_MASA_REPORT.XLS"
Src2 = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\AX02_MASA_REPORT.XLS"
Dim xlObj As Object
Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open Src1
xlObj.Visible = True
xlObj.DisplayAlerts = False
xlObj.ActiveWorkbook.SaveAs Src2, FileFormat:=51
xlObj.Quit
Set xlObj = Nothing
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, 10, "MaSAT", Src2, True, ""
DoCmd.DeleteObject acTable, "AX02_MASA_REPORT$_ImportErrors"
DoCmd.SetWarnings True
End Sub
Public Sub HX01_MASA_REPORT()
Dim sPath As String, Src1 As String, Src2 As String
sPath = "\\Frxfileserv1\FTPDOWN\WW_PUBLIC\Download Folder\New Product Programs-MASA\Local\HX01" & "\"
Src1 = sPath & "HX01_MASA_REPORT.XLS"
Src2 = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\HX01_MASA_REPORT.XLS"
Dim xlObj As Object
Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open Src1
xlObj.Visible = True
xlObj.DisplayAlerts = False
xlObj.ActiveWorkbook.SaveAs Src2, FileFormat:=51
xlObj.Quit
Set xlObj = Nothing
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, 10, "MaSAT", Src2, True, ""
DoCmd.DeleteObject acTable, "HX01_MASA_REPORT$_ImportErrors"
DoCmd.SetWarnings True
End Sub
Public Sub JL01_MASA_REPORT()
Dim sPath As String, Src1 As String, Src2 As String
sPath = "\\Frxfileserv1\FTPDOWN\WW_PUBLIC\Download Folder\New Product Programs-MASA\Local\JL01" & "\"
Src1 = sPath & "JL01_MASA_REPORT.XLS"
Src2 = "G:\XE_ECMs\__MOST_REPORT\Reservation Data\JL01_MASA_REPORT.XLS"
Dim xlObj As Object
Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open Src1
xlObj.Visible = True
xlObj.DisplayAlerts = False
xlObj.ActiveWorkbook.SaveAs Src2, FileFormat:=51
xlObj.Quit
Set xlObj = Nothing
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, 10, "MaSAT", Src2, True, ""
DoCmd.DeleteObject acTable, "JL01_MASA_REPORT$_ImportErrors"
DoCmd.SetWarnings True
End Sub
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE