Results 1 to 7 of 7
  1. #1
    TG_W is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2010
    Location
    Manvel, TX
    Posts
    299

    VBA to save XML file to XLSX that is selected by VBA

    I have a database that needs to import files that are exported from another system to create multiple different reports. The system is exporting spreadsheets into .xml format, and I would like to let the users select multiple files at once from a dialog box.

    The process is idealized to open an .xml file, delete the first 5 rows, save the .xml file as an .xlsx, then import the .xlsx file (append to a current table). I am currently able to call the .xml file and delete the first five rows, and that is where I am stuck.



    I might be trying to cram too much into one step, but I would like to remove as much work from the users as possible. I have included my code below.


    Code:
    DoCmd.RunSQL ("Delete * from tblImport")
        Dim fd As FileDialog
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        Dim vrtSelectedItem As Variant
        With fd
                
        .AllowMultiSelect = True
                
        .Filters.Clear
        .Filters.Add "Master Document Registers", "*.xls?;*.xml", 1
                
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
                Dim wb As Excel.Workbook
                Dim xlApp As Excel.Application
                Dim wst As Worksheet
                Set xlApp = CreateObject("Excel.Application")
                xlApp.Visible = True
                Set wb = xlApp.Workbooks.Open(vrtSelectedItem, True, False)
                For Each wst In wb.Worksheets
                    wst.Rows(5).Delete
                    wst.Rows(4).Delete
                    wst.Rows(3).Delete
                    wst.Rows(2).Delete
                    wst.Rows(1).Delete
                    Next wst
                    wb.SaveAs vrtSelectedItem, xlExcel12
                    wb.Close True
                    
                    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblDocuments", vrtSelectedItem & ".xlsx", True
                    
                    Next vrtSelectedItem
                    
            Else: End If
        End With
        Set fd = Nothing
    End Sub
    Last edited by TG_W; 07-23-2013 at 11:54 AM.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What do you mean by 'stuck', what happens - error message, wrong results, nothing happens?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    TG_W is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2010
    Location
    Manvel, TX
    Posts
    299
    Apologies. Brain is fried from working on it all day.

    I am able to get the files opened and edited, but they continue to save in .xml. If I could at least get to the point of saving in Excel, I don't mind making the end users grab the .xlsx, but I can't get it to save how I would like making an easier import. I gave up on .xml import working right after a few hours this morning.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You open selected xml file into Excel and edit then save using the same variable for the file name, which includes xml extension?

    Try: wb.SaveAs Left(vrtSelectedItem, InStrRev(vrtSelectedItem, ".")) & "xls", xlExcel12

    I am not familiar with acSpreadsheetTypeExcel12Xml parameter. Why not just acSpreadsheetTypeExcel12?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    TG_W is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2010
    Location
    Manvel, TX
    Posts
    299
    That did it! I used the same Left string to call for the import, as well.

    I copied that import line from another database I had built a year or so ago and haven't the foggiest why I chose to use "...xml". I pulled it from the code and it all worked like a charm.

    I'm posting the final code below for anyone else that might want/need to do the same for their data.

    Code:
        response = MsgBox("Please confirm import request", vbYesNo, "Confirm Delete")
            If response = vbYes Then
                DoCmd.CopyObject , "tblDocuments - " & Format(Now(), "dd-mmm-yy"), acTable, "tblDocuments"
                DoCmd.RunSQL ("Delete * from tblImport")
                DoCmd.RunSQL ("Delete * from tblRptGrp")
            
                'Declare a variable as a FileDialog object.
            Dim fd As FileDialog
    
    
            'Create a FileDialog object as a File Picker dialog box.
            Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    
            Dim vrtSelectedItem As Variant
    
    
            With fd
                
                .AllowMultiSelect = True
                
                .Filters.Clear
                .Filters.Add "Master Document Registers", "*.xls?;*.xml", 1
                
                If .Show = -1 Then
    
    
                    For Each vrtSelectedItem In .SelectedItems
    
    
                        Dim wb As Excel.Workbook
                        Dim xlApp As Excel.Application
                        Dim wst As Worksheet
                        Dim wstCt As Long
                        Dim colWorksheets As Collection
                        
                        Set xlApp = CreateObject("Excel.Application")
                        xlApp.Visible = True
    
    
                        Set colWorksheets = New Collection
                        Set wb = xlApp.Workbooks.Open(vrtSelectedItem, True, False)
                        For Each wst In wb.Worksheets
                            wst.Rows(5).Delete
                            wst.Rows(4).Delete
                            wst.Rows(3).Delete
                            wst.Rows(2).Delete
                            wst.Rows(1).Delete
                        Next wst
                        wb.SaveAs Left(vrtSelectedItem, InStrRev(vrtSelectedItem, ".")) & "xls", xlExcel12
                        For wstCt = 1 To wb.Worksheets.Count
                            colWorksheets.Add wb.Worksheets(wstCt).Name
                        Next wstCt
                        
                        For wstCt = colWorksheets.Count To 1 Step -1
                            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
                                "tblImport", Left(vrtSelectedItem, InStrRev(vrtSelectedItem, ".")) & "xls", True, colWorksheets(wstCt) & "$"
                        Next wstCt
                        
                    Next vrtSelectedItem
                    xlApp.Quit
                'The user pressed Cancel.
                Else: End If
            End With
    
    
            Set fd = Nothing
            Else: End If
            
            MsgBox "Import complete.", vbInformation, "Done."
    Last edited by TG_W; 07-23-2013 at 11:56 AM.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Glad it is working.

    BTW, include a / in the end CODE tag, like [/CODE]
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    TG_W is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2010
    Location
    Manvel, TX
    Posts
    299
    Fixed and updated the code to import all of the worksheets in the workbook.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 02-07-2013, 03:15 PM
  2. Import Specific cells from Excel based on selected file.
    By Only4Access in forum Programming
    Replies: 5
    Last Post: 02-29-2012, 02:32 AM
  3. Open Excel .xlsx file from Access
    By Bigmix in forum Programming
    Replies: 6
    Last Post: 02-28-2012, 09:55 AM
  4. Replies: 1
    Last Post: 12-02-2011, 10:43 AM
  5. Save imagebox to file?
    By EmSox in forum Forms
    Replies: 0
    Last Post: 07-28-2010, 10:32 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums