UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sub To Import Excel File Help, Access 2016    
 
   
LagoDavid
post Dec 18 2017, 01:09 PM
Post#1



Posts: 446
Joined: 12-October 03
From: Texas


Using a previous thread I found via SEARCH, I modified some code to attach to the click event of a command button. After about three hours of failed effort, I need help please. I routinely import an Excel file that comes from our main lab with analytical data. I import the data into a temporary table and then move the data to permanent tables. I have been doing this with the Import Wizard. I need to automate this so others can do it, so I created a form with a command button and added the following code (copied from UA and then modified by me), trying to use the File Dialog box to import the Excel file into the temporary table. I am having three problems with the code.
1. I am unable to get the filter property to work on the file dialog box (.Filters.Add "Excel", "*.xls", "*.xlsx")
2. I have an IF statement that I had to comment out because it would fail and cancel the import. The IF statement is supposed to determine if the Excel file is an .xls or an .xlsx and then correctly set the property for the TransferSpreadsheet command by checking for the last character of the file name ("s" or "x"). It finds neither and falls to the final ELSE which cancels the import.
3. With the above IF statement commented out and forcing the type to Excel 12, the sub goes to completion, but the temporary table is never created. I don't get any error message, but there is no table created from the Excel file.

Here is my code:
Private Sub cmdImportFile_Click
On Error Resume Next

Dim fdFilePick As FileDialog 'requires the Reference to the Microsoft Office Object Library via VBE TOOLS/References (Microsoft Office 16.0 Object Library)
Dim varSelectedFile As Variant ' variant variable to get and temporarily hold the name/names of the selected files in the file dialog box
Dim strExcelImportFile As String ' string variable used to hold the name of the selected Excel file using the Cstr function
Dim strMyTableName As String ' string variable to hold the name of the temporary table that will be created from the imported Excel file
Dim strExcelFileType As String ' will later need to determine which version of Excel was used for the Excel import file'

strMyTableName = "ALIMSImport" 'the temporary table name'
Set fdFilePick = Application.FileDialog(msoFileDialogFilePicker) 'uses MS Office object library built in File Picker dialog box'
DoCmd.Hourglass True

With fdFilePick 'set the various properties of the built in File Picker dialog box'
.AllowMultiSelect = False 'user is only allowed to select one file name in the File Picker dialog box
.ButtonName = "&Import" 'this names the caption that appears on the built in File Picker Dialog form
.InitialFileName = Application.CurrentProject.path 'this sets the default directory path to the current directory and displays in the File Picker Dialog box
.Filters.Add "Excel", "*.xls", "*.xlsx" 'this filters the Dialog box to only display files of Excel type, only it does not work

If .Show Then '.Show property of the File Picker dialog default setting is TRUE. This line calls the Dialog box to display on screen
strExcelImportFile = CStr(varSelectedFile)
'now delete the temporary table if it is already present
'if the table is not already present the following line will error, but On Error Resume Next above allows processing to continue
DoCmd.DeleteObject acTable, strMyTableName
'at this point check to see if the file is xls or xlsx'
If Right(strExcelImportFile,1) = "x" Then 'file extension is .xlsx and type is Excel 12; this is the IF STATEMENT I had to comment out'
strExcelFileType = "acSpreadsheetTypeExcel12"
ElseIf Right(strExcelImportFile,1) = "s" Then 'file extension is .xls and type is Excel 9'
strExcelFileType = "acSpreadsheetTypeExcel9"
Else
MsgBox "File extension must be either .xls or .xlxs using the FILE SAVE AS command in Excel.", vbCritical, "ImportCanceled!"
Exit SUB
End if
DoCmd.TransferSpreadsheet acImport, strExcelFileType, strMyTableName, strExcelImportFile, True
DoEvents
Else
MsgBox "Import Process Canceled", vbCritical, "Import Canceled"
Exit SUB
End If
End With
End SUB
Go to the top of the page
 
LagoDavid
post Dec 18 2017, 01:11 PM
Post#2



Posts: 446
Joined: 12-October 03
From: Texas


and BTW, how do copy my code into my message and get the code to maintain the indentations so it is easier to read?
Go to the top of the page
 
GroverParkGeorge
post Dec 18 2017, 01:15 PM
Post#3


UA Admin
Posts: 33,794
Joined: 20-June 02
From: Newcastle, WA


Attached File  Codewrapper.jpg ( 20.42K )Number of downloads: 2
Go to the top of the page
 
LagoDavid
post Dec 18 2017, 01:28 PM
Post#4



Posts: 446
Joined: 12-October 03
From: Texas


CODE
Private Sub cmdImportFile_Click
    On Error Resume Next

    Dim fdFilePick As FileDialog   'requires the Reference to the Microsoft Office Object Library via VBE TOOLS/References (Microsoft Office 16.0 Object Library)
    Dim varSelectedFile As Variant  ' variant variable to get and temporarily hold the name/names of the selected files in the file dialog box
    Dim strExcelImportFile As String  ' string variable used to hold the name of the selected Excel file using the Cstr function
    Dim strMyTableName As String  ' string variable to hold the name of the temporary table that will be created from the imported Excel file
    Dim strExcelFileType As String ' will later need to determine which version of Excel was used for the Excel import file'
    
    strMyTableName = "ALIMSImport"  'the temporary table name'
    Set fdFilePick = Application.FileDialog(msoFileDialogFilePicker)  'uses MS Office object library built in File Picker dialog box'
    DoCmd.Hourglass True
    
    With fdFilePick  'set the various properties of the built in File Picker dialog box'
        .AllowMultiSelect = False   'user is only allowed to select one file name in the File Picker dialog box
        .ButtonName = "&Import"     'this names the caption that appears on the built in File Picker Dialog form
        .InitialFileName = Application.CurrentProject.path  'this sets the default directory path to the current directory and displays in the File Picker Dialog box
        .Filters.Add "Excel", "*.xls", "*.xlsx"    'this filters the Dialog box to only display files of Excel type
  
        If .Show Then   '.Show property of the File Picker dialog default setting is TRUE.  This line calls the Dialog box to display on screen
            strExcelImportFile = CStr(varSelectedFile)    
                'now delete the temporary table if it is already present
                'if the table is not already present the following line will error, but On Error Resume Next above allows processing to continue
            DoCmd.DeleteObject acTable, strMyTableName  
                'at this point check to see if the file is xls or xlsx'
            If Right(strExcelImportFile,1) = "x" Then 'file extension is .xlsx and type is Excel 12'
                strExcelFileType = "acSpreadsheetTypeExcel12"
                ElseIf Right(strExcelImportFile,1) = "s" Then 'file extension is .xls and type is Excel 9'
                    strExcelFileType = "acSpreadsheetTypeExcel9"
                Else
                    MsgBox "File extension must be either .xls or .xlxs using the FILE SAVE AS command in Excel.", vbCritical, "ImportCanceled!"
                    Exit SUB
            End if
            DoCmd.TransferSpreadsheet acImport, strExcelFileType, strMyTableName, strExcelImportFile, True
            DoEvents            
        Else  
            MsgBox "Import Process Canceled", vbCritical, "Import Canceled"
            Exit SUB
        End If
    End With
End SUB
Go to the top of the page
 
River59
post Dec 18 2017, 02:21 PM
Post#5



Posts: 1,403
Joined: 7-April 10
From: Detroit, MI


I don't think you have to change for the type of Excel file. I don't in my databases and they work fine. Just try acSpreadsheetTypeExcel8

CODE
Private Sub IMPORT_PULL_LIST_Click()

Import_Completed.Visible = False
  
   Dim xe As Excel.Application
   Dim fDialog As Office.FileDialog
   Dim varFile As String
    Set xe = New Excel.Application
    
   'Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      'Allow user to make multiple selections in dialog box.
      .AllowMultiSelect = False
            
      'Set the title of the dialog box.
      .Title = "Please select Pull List Data File"
      
      'Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Excel Files", "*.xls; *.xlsx"
      .InitialFileName = CurrentProject.Path
      'Show the dialog box. If the .Show method returns True, the
      'user picked at least one file. If the .Show method returns
      'False, the user clicked Cancel.
      If .Show = True Then
         'Loop through each file selected and add it to the list box.
         varFile = .SelectedItems(1)
      Else
         Exit Sub
      End If
   End With

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, tablename:="Pull List_Temp", FileName:=varFile, HasFieldNames:=True, Range:="Claims_Table!A:BB"
Go to the top of the page
 
LagoDavid
post Dec 18 2017, 03:42 PM
Post#6



Posts: 446
Joined: 12-October 03
From: Texas


thank you!
With your example I was able to sift through my code and find a couple of errors that were preventing it from working. For one, I had not used the SELECTEDITEMS method anywhere and thus had never set the name of the file to be imported. I had also used the wrong syntax for the filter. Your code also demonstrated for me how to place some qualifying text in the TransferSpreadsheet function so the parameters would make more sense.

And your suggestion about just using type Excel8 seems to work fine. Why would there be so many Excel types then?

My new code:
CODE
Private Sub cmdImportFile_Click()
    On Error Resume Next

    Dim fdFileSelector As Office.FileDialog   'requires the Reference to the Microsoft Office Object Library via VBE TOOLS/References (Microsoft Office 16.0 Object Library)
    'Dim varSelectedFile As Variant  ' variant variable to get and temporarily hold the name/names of the selected files in the file dialog box
    Dim strExcelImportFile As String  ' string variable used to hold the name of the selected Excel file using the Cstr function
    Dim strMyTableName As String  ' string variable to hold the name of the temporary table that will be created from the imported Excel file
    Dim strExcelFileType As String ' will later need to determine which version of Excel was used for the Excel import file'
    
    strMyTableName = "ALIMSImport"  'the temporary table name'
    Set fdFileSelector= Application.FileDialog(msoFileDialogFilePicker)  'uses MS Office object library built in File Picker dialog box'
    DoCmd.Hourglass(True)
    
    With fdFileSelector 'set the various properties of the Office FilePicker dialog box'
        .AllowMultiSelect = False   'user is only allowed to select one file name in the FilePicker dialog box
        .Title = "Please select the Excel file to import"
        .ButtonName = "&Import"     'this names the caption that appears on the built in FilePicker Dialog form
        .Filters.clear 'clear out the current filters to add new filters'
        .Filters.Add "Excel", "*.xls; *.xlsx"    'this filters the Dialog box to only display files of Excel type
        .InitialFileName = Application.CurrentProject.path  'this sets the default directory path to the current directory and displays in the File Picker Dialog box
  
        If .Show Then   'If .Show returns True, the user picked at least one file. If the .Show method returns False, the user clicked Cancel.
            strExcelImportFile = .SelectedItems(1)    
                'now delete the temporary table if it is already present
                'if the table is not already present the following line will error, but On Error Resume Next above allows processing to continue
            DoCmd.DeleteObject acTable, strMyTableName  
                'at this point check to see if the file is xls or xlsx'
''            If Right(strExcelImportFile,1) = "x" Then 'file extension is .xlsx and type is Excel 12; this entire IF statement is now commented out
''                strExcelFileType = "acSpreadsheetTypeExcel12"
''                ElseIf Right(strExcelImportFile,1) = "s" Then 'file extension is .xls and type is Excel 9'
''                    strExcelFileType = "acSpreadsheetTypeExcel9"
''                Else
''                    MsgBox "File extension must be either .xls or .xlxs using the FILE SAVE AS command in Excel.", vbCritical, "ImportCanceled!"
''                    Exit SUB
''            End if
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, tablename:=strMyTableName, FileName:=strExcelImportFile, HasFieldNames:=True
            DoEvents            
        Else  
            MsgBox "Import Process Canceled", vbCritical, "Import Canceled"
            Exit SUB
        End If
    End With
    DoCmd.Hourglass(False)
End SUB
Go to the top of the page
 
doctor9
post Dec 18 2017, 03:44 PM
Post#7


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


LagoDavid,

For backwards compatibility: Check it out

Dennis
Go to the top of the page
 
LagoDavid
post Dec 18 2017, 04:02 PM
Post#8



Posts: 446
Joined: 12-October 03
From: Texas


Yes, that is what I had read previously. However, in this code block, I am using Excel8 as was suggested by River59, and it works to import an Excel 12 file (.xlsx). ???

I had originally included the IF block to use the RIGHT function to determine if the file as .xls or .xlsx. (9 or 12). I could not make my IF work correctly. Instead then, I used 8 as was suggested and successfully imported an Excel 12 file. Was this perhaps only because the Excel file was very simple and only contains one worksheet?

Also, my .ButtonName method is not working. The dialog box shows OPEN as the button name instead of IMPORT as I was hoping. Any idea why?
Go to the top of the page
 
doctor9
post Dec 18 2017, 04:05 PM
Post#9


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


LagoDavid,

> I had originally included the IF block to use the RIGHT function to determine if the file as .xls or .xlsx. (9 or 12). I could not make my IF work correctly

Try this:

CODE
Select Case Right(strExcelImportFile, 4)
Case ".xls"
    strExcelFileType = "acSpreadsheetTypeExcel9"
Case "xlsx"
    strExcelFileType = "acSpreadsheetTypeExcel12"
Case Else
    strExcelFileType = "I don't know what this file is!"
End Select

It may not be applicable here, but the extension MAY be wrong, due to user error when saving the file. I personally deal with a vendor that provides a piece of software that saves an HTML document with an .XLS extension, just because it's meant to be opened in Excel. Excel complains when you open the file, but it eventually opens, which seems to be good enough for that vendor. (insert eye roll here.)

Hope this helps,

Dennis
Go to the top of the page
 
LagoDavid
post Dec 18 2017, 04:28 PM
Post#10



Posts: 446
Joined: 12-October 03
From: Texas


Perfect. Thank you. it works as a select case, where it did not work as an IF.

Now I have come up with another issue. This does not work if my temporary table (ALIMSImport) is open before the sub starts. How do I add lines in the beginning of the sub to close ALIMSImport if it is open?
Go to the top of the page
 
doctor9
post Dec 18 2017, 04:35 PM
Post#11


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


LagoDavid,

After a cursory glance at your code, I'm assuming that you mean that you aren't able to Delete the table because it's in use.

Personally, I'd stay away from deleting and re-creating the table if it's always going to have the same structure. Just delete the contents instead of deleting the table.

CODE
CurrentDb.Execute "DELETE * FROM ALIMSImport;", dbFailOnError


Hope this helps,

Dennis
Go to the top of the page
 
LagoDavid
post Dec 18 2017, 04:52 PM
Post#12



Posts: 446
Joined: 12-October 03
From: Texas


OOPS. I was wrong it does not work with the Select Case statement. It only worked because I had earlier hard coded acSpreadsheetTypeExcel8 into the TransferSpreadsheet parameter. Once I replaced that hard code parameter with a variable it quit creating the new temporary table.

Here is the complete code:
CODE
Private Sub cmdImportFile_Click()
    On Error Resume Next

    Dim fdFileSelector As Office.FileDialog   'requires the Reference to the Microsoft Office Object Library via VBE TOOLS/References (Microsoft Office 16.0 Object Library)
    'Dim varSelectedFile As Variant  ' variant variable to get and temporarily hold the name/names of the selected files in the file dialog box
    Dim strExcelImportFile As String  ' string variable used to hold the name of the selected Excel file using the Cstr function
    Dim strTemporaryTableName As String  ' string variable to hold the name of the temporary table that will be created from the imported Excel file
    Dim strExcelFileType As String ' will later need to determine which version of Excel was used for the Excel import file'
    
    strTemporaryTableName = "ALIMSImport"  'the temporary table name'
    Set fdFileSelector= Application.FileDialog(msoFileDialogFilePicker)  'uses MS Office object library built in File Picker dialog box'
    DoCmd.Hourglass(True)
    
    With fdFileSelector 'set the various properties of the Office FilePicker dialog box'
        .AllowMultiSelect = False   'user is only allowed to select one file name in the FilePicker dialog box
        .Title = "Please select the Excel file to import"
        .ButtonName = "&Import"     'this names the caption that appears on the built in FilePicker Dialog form
        .Filters.clear 'clear out the current filters to add new filters'
        .Filters.Add "Excel", "*.xls; *.xlsx"    'this filters the Dialog box to only display files of Excel type
        .InitialFileName = Application.CurrentProject.path  'this sets the default directory path to the current directory and displays in the File Picker Dialog box
  
        If .Show Then   'If .Show returns True, the user picked at least one file. If the .Show method returns False, the user clicked Cancel.
            strExcelImportFile = .SelectedItems(1)    
                'now delete the temporary table if it is already present
                'if the table is not already present the following line will error, but On Error Resume Next above allows processing to continue
            DoCmd.DeleteObject acTable, strTemporaryTableName
            
                'at this point check to see if the file is xls or xlsx to use the appropriate parameter in the TransferSpreadsheet function'                
            Select Case Right(strExcelImportFile, 4)
                Case ".xls"
                    strExcelFileType = "acSpreadsheetTypeExcel9"
                Case "xlsx"
                    strExcelFileType = "acSpreadsheetTypeExcel12"
                Case Else
                    MsgBox "File extension must be either .xls or .xlxs using the FILE SAVE AS command in Excel.", vbCritical, "ImportCanceled!"
                    Exit Sub
         End Select

         DoCmd.TransferSpreadsheet acImport, SpreadsheetType:=strExcelFileType, tablename:=strTemporaryTableName, FileName:=strExcelImportFile, HasFieldNames:=True
            DoEvents            
        Else  
            MsgBox "Import Process Canceled", vbCritical, "Import Canceled"
            Exit SUB
        End If
    End With
    DoCmd.Hourglass(False)
End SUB
Go to the top of the page
 
doctor9
post Dec 18 2017, 04:57 PM
Post#13


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


LagoDavid,

Okay, here's the deal. The spreadsheet types are actually integer constants that are built into the Excel object library. In short, you should not assign these values to a string, you should assign them to an integer.

CODE
Dim intExcelFileType as Integer

Select Case Right(strExcelImportFile, 4)
Case ".xls"
    intExcelFileType = acSpreadsheetTypeExcel9
Case "xlsx"
    intExcelFileType = acSpreadsheetTypeExcel12
Case Else
    MsgBox "I cannot determine the import file type!  Aborting."
    Exit Sub
End Select

DoCmd.TransferSpreadsheet acImport, SpreadsheetType:=intExcelFileType, tablename:=strTemporaryTableName, FileName:=strExcelImportFile, HasFieldNames:=True


Hope this helps,

Dennis
Go to the top of the page
 
LagoDavid
post Dec 18 2017, 05:06 PM
Post#14



Posts: 446
Joined: 12-October 03
From: Texas


OK, I understand. And it does work now. Thank you so much!

And I will change the code to just delete the records instead of deleting the table.
Go to the top of the page
 
River59
post Dec 19 2017, 08:56 AM
Post#15



Posts: 1,403
Joined: 7-April 10
From: Detroit, MI


The Excel8 is a lower version so it will work with anything after that. These databases were built in Access 2007. My Excel workbooks contain multiple worksheets. I showed you a stripped down version that only imported one of these worksheets (Pull List). There are 4 more.

Glad the example was of help. Now go have fun!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2018 - 07:51 AM