none
Third Party reports to formatted excel files RRS feed

  • Question

  • This may or may not be a simple question as it involves three different platforms and an unusual way of getting things done. I have some experience with access database and big data but needs some direction:

    In short, I need to take a report from a third party source, run a query on this data to pull out needed information, format this information into a relatively specific format then export to excel so I can then print/distribute.

    For example: I need to take a third party report that comes in excel format (this report is not aesthetically pleasing what so ever) for inventory of a specific shelter containing some equipment. Would like to use access as a database to store the information from the report then use some sort of query (open to other options other than a query) to pick and pull information into a certain format then export back to excel  

    Wednesday, July 12, 2017 2:24 PM

All replies

  • Hi James,

    Access and Excel are used for different purposes. If the data in Excel lends itself into a database structure, then it shouldn't be a problem importing it into Access, manipulate the data, and then export it back to Excel for formatting or charting. However, if the data in Excel does not easily represent a database table, then you'll have a hard time importing the data into Access. It might still be possible, but you'll have to do a lot of work.

    Just my 2 cents...

    Wednesday, July 12, 2017 3:35 PM
  • You might be able to link the source excel file and import the data in that manner or perhaps so simple Excel automation could be utilized, but without concrete examples of what your inputs and desired outputs are it is next to impossible to better guide you.

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, July 12, 2017 4:49 PM
  • Hi Young James II,

    you only mentioned the requirement but you did not mentioned how you want to achieve.

    do you want the solution by using user interface or by using VBA code?

    from your over all description , I understand that your report comes from the third party source is in Excel format.

    so we not need to care from where , how it generated from which third party source. just need to make sure that it is in proper format and we can open that file in Excel without any issue.

    if you want to get the solution by user interface then you can follow the approach below.

    to import the data from Excel to Access.

    Import or link to data in an Excel workbook

    Exchange (copy, import, export) data between Excel and Access

    Import data into an Access database

    Move data from Excel to Access

    after importing the data you can fire a query to get the desired result.

    then to again export the data to Excel you can refer links below.

    Export data to Excel

    if you want to solve the issue by VBA code then you can refer approach below.

    to import the data from Excel to Access.

    Sub demo()
    
     Dim WrksheetName As String
     Dim i As Integer
     Dim xl As Object
     Set xl = CreateObject("Excel.Application")
    
     xl.Visible = True
     xl.Workbooks.Open "c:\temp\filename.xls"
    
     With xl
     .Visible = True
     With .Workbooks(.Workbooks.Count)
     For i = 1 To .Worksheets.Count
     WrksheetName = .Worksheets(i).NAME
     DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, WrksheetName, "c:\temp\filename.xls"
     Next i
     End With
    
     End With
     Set xl = Nothing
    
     End Sub

    Reference:

    DoCmd.TransferSpreadsheet Method (Access)

    then you can fire the query on the data and generate the result.

    to Export the data from Access to Excel , we will use same method that we use to import data with 'acExport' parameter.

    Sub demo() On Error GoTo ErrorHandler Dim dbTable As String Dim xlWorksheetPath As String xlWorksheetPath = “C:\” xlWorksheetPath = xlWorksheetPath & “xlWorkbookName.xlsx”

    dbTable = “tblMaster” DoCmd.TransferSpreadsheet transfertype:=acExport, spreadsheettype:=acSpreadsheetTypeExcel12, tablename:=dbTable, FileName:=xlWorksheetPath, hasfieldnames:=True ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox “Error No: ” & Err.Number & “;Description: ” & Err.Description Resume ErrorHandlerExit End Sub

    try to implement the desired approach and let us know if you have any problem to implement it.

    we will try to provide further suggestion to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 13, 2017 1:21 AM
    Moderator
  • I would agree with the other suggestions. Creating a linked table to the Excel Workbook report would be the easiest method. If the Worksheet in the Workbook contains a named Range within the report that represents only the data you can probably link to that directly. Otherwise, you need to know or be able to identify where the data is located so that it can be queried. This is also possible using DAO or ADO and SQL.

    https://blogs.msdn.microsoft.com/frice/2004/07/09/using-ado-with-excel-data-sources/


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, July 13, 2017 12:44 PM