Import Module Code Bug Problem

[Ace2014]

Hello everyone!

I'm trying to automate the import excel specific spreadsheet function.
I want import it as a new table with a specific name.

Here's my code, the bug is highlighted:

Thanks is advance!

Option Compare Database

'Name macro
Sub Upload_PPNBench()


'Define strTable type: Access Table that will be uploaded
Dim strTable As String


'Define strFilePath type: Path to Excel .xls that will be uploaded into Access
Dim strFilePath As String


'Opens browse computer application
With Application.FileDialog(msoFileDialogFilePicker)


'Select one file at a time
    .AllowMultiSelect = False


'Browser Title
    .Title = "Locate a Excel file to Import"


'Name what the button command will do: Imports Excel file to Access
    .ButtonName = "Import"
    
'Clear previous selection
    .Filters.Clear


'Browser only works with .xls files, add ".xls" to Excel file
    .Filters.Add "Excel", "*.xls"
    
'Location Browser begins
    .InitialFileName = "C:\Test"
    
'Define Browser vie: thumbnail
    .InitialView = msoFileDialogViewThumbnail
        
'If nothing is selected, have error sign appear
        If .Show = -1 Then
        strFilePath = Trim(.SelectedItems(1))
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel2, strTable, strFilePath, True, "tbl4_Upload_PPNBench"
        Else
        Exit Sub
        End If
        End With
    End Sub

[Ace2014]

Figured it out

Excel 3 : acSpreadsheetTypeExcel3
Excel 95 : acSpreadsheetTypeExcel7
Excel 97 : acSpreadsheetTypeExcel8
Excel 2000 : acSpreadsheetTypeExcel9 (Default Value!)
Excel 2002 : acSpreadsheetTypeExcel10
Excel 2003 : acSpreadsheetTypeExcel11
http://www.pcreview.co.uk/forums/acspreadsheettypeexcel-t1675881.html

[Ace2014]

The above didn't help

[June7]

What is tbl4_UploadPPNBench? A named range in spreadsheet? That should work.

What is the exact error message?

[Ace2014]

Hi,

"tbl4_" is a spreadsheet in my Excel

So, I saw that I hadn't defined the variables I used, so I added to the code.
Now I'm not getting an error code.

But, I don't have the table I want the data from Excel to go to in Access.

I want to create a new table.
I think my code only allows for appending at the moment and I don't know how get it to upload a new table.

'Name macroSub Upload_PPNBench()


'Define strTable type: Access Table that will be uploaded
Dim strTable As String


'Define strFilePath type: Path to Excel .xls that will be uploaded into Access
Dim strFilePath As String


'Opens browse computer application
With Application.FileDialog(msoFileDialogFilePicker)


'Select one file at a time
    .AllowMultiSelect = False


'Browser Title
    .Title = "Locate a Excel file to Import"


'Name what the button command will do: Imports Excel file to Access
    .ButtonName = "Import"
    
'Clear previous selection
    .Filters.Clear


'Browser only works with .xls files, add ".xls" to Excel file
    .Filters.Add "Excel", "*.xls"
    
'Location Browser begins
    .InitialFileName = "C:\Test"
    
'Define Browser vie: thumbnail
    .InitialView = msoFileDialogViewThumbnail
        
'If nothing is selected, have error sign appear
        If .Show = -1 Then
        Exit Sub
        
'If file is selcted, do following
        End If


'Save the first file selected
    strFilePath = Trim(.SelectedItems(1))
    End With


'Define strTable as what table in Access
    strTable = "tbl4_PNNBench"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTable, strFilePath, True, "tbl4_Upload_PPNBench"
      
    End Sub

[June7]

I don't think the Range argument accepts just a sheet name.

What is the exact error message?

Step debug. Review link at bottom of my post.

[Ace2014]

I don't think the Range argument accepts just a sheet name.

What is the exact error message?

Step debug. Review link at bottom of my post.

It's still 48KB after I compress/repair and zip.
But now I'm not getting any error, it just isn't uploading my spreadsheet
I should be able to upload a spreadsheet, I've already been appending excel spreadsheets to pre-existing tables, but for this one, I want to create a table so I can run a macro that would:
create a table, then a query, and then export said query to excel.

Could I send it to you by email?

[June7]

Can upload to a fileshare site such as Box.com and post link to file. Include the spreadsheet.

[Ace2014]

Can upload to a fileshare site such as Box.com and post link to file. Include the spreadsheet.

Excel
https://app.box.com/s/h9rbpy0xq6szaw6y7cu9

Access
https://app.box.com/s/ed4r0tioz9po5a2162p1

[June7]

This code to select filepath works:

Dim varFile As Variant

    If .Show = -1 Then

        For Each varFile In .SelectedItems
            'Save the first file selected
            strFilePath = Trim(.SelectedItems(1))
        Next

        'Define strTable as what table in Access
        strTable = "tbl4_PNNBench"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTable, strFilePath, True, "tbl4_Upload_PPNBench"

    End If

However, apparently TransferSpreadsheet method requires a table to already exist. Whereas the import wizard does not but the wizard will take the spreadsheet name as the table name.

Why would you want to create a new table with each data import? Why not import to existing table then use query to filter to these new records?

[Ace2014]

This code to select filepath works:

Dim varFile As Variant

    If .Show = -1 Then

        For Each varFile In .SelectedItems
            'Save the first file selected
            strFilePath = Trim(.SelectedItems(1))
        Next

        'Define strTable as what table in Access
        strTable = "tbl4_PNNBench"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTable, strFilePath, True, "tbl4_Upload_PPNBench"

    End If

However, apparently TransferSpreadsheet method requires a table to already exist. Whereas the import wizard does not but the wizard will take the spreadsheet name as the table name.

Why would you want to create a new table with each data import? Why not import to existing table then use query to filter to these new records?

Well, the difficulty lies in that I'm trying to automate the process of narrowing a knowledge database's scope and exporting it to Excel.

Process
-Click button
-Choose excel file
-Automatically import spreadsheet table
-Use table to query relational data in the knowledge database
-Export query results to Excel

I thought it'd be easiest to connect a new table every time to a "pre-made query" and just replace the data. If that's not possible, I can always create a table, append the information the users want to extract and then finish with a delete query to clear the contents.

What do you think?

[June7]

How could the new table connect to a 'pre-made query' - the table didn't exist for the query to use.

[Ace2014]

If the table has a name in the sql with the same header connecting it to the other datasource. I didn't think I'd be that difficult. The modules & macros would build the query after the table has been uploaded.

[June7]

I don't under the first statement.

Yes, code can create a query object and then delete it. I just think you are making this more complicated than need be. Also, I don't think an approach that creates and deletes objects cannot be used in an executable version of database - in case you have thoughts of doing that in the future.

The thread is marked solved - is it?

[Ace2014]

Well, I marked it as solved because I decided to have a table in Access where the Excel data would be uploaded.
Then I'd requery the information and export it into excel and then run a delete query for all fields where they are not null.

Do you have a better recommendation for how to do this?
I need the users to be able to change the information in the table connected to the query as easily as possible.
Then requery, export to excel, and then ensure the data is cleared for the following user.