Need Code to Automate ImportExportSpreadsheets

[Ace2014]

That's a good question. The code is specific to xlsx and even the Filepicker should filter to only that type. However, something is amiss. Perhaps to expand on making a new file, you could make a new CSV file and try importing that.

AS for the link in the previous post, do not know why you would need to edit your registry or even consider it.

Sorry it's been a while. Thanks so much for your help.



My code is for a .xlsx file, it won't work if I choose another file type.
I've tried importing my template .xlsx spreadsheet 2 times now into Access. Both have been met with the same F1, F2, etc error

Here is my code:

Option Compare Database

Sub Import()
Dim strTable As String
Dim strFilePath As String


With Application.FileDialog(msoFileDialogFilePicker)


    .AllowMultiSelect = False
    .Title = "Locate a Excel file to Import"
    .ButtonName = "Import"
    .Filters.Clear
    .Filters.Add "Excel", "*.xlsx"
    .InitialFileName = "C:\Test"
    .InitialView = msoFileDialogViewThumbnail
    
        If .Show = 0 Then
        'There is a problem
        Exit Sub
        End If
    
    'Save the first file selected
    strFilePath = Trim(.SelectedItems(1))
    
End With


strTable = "tbl1_PartPricingDataAggregation"


DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, strTable, strFilePath, False, "A1:L1000"


End Sub

[rpeare]

any chance of getting an example excel file and/or database, I don't see a reason in the code to explain it basically duplicating entries on entirely different sets of fields.

[Ace2014]

Could you give me your email address? I'm not sure if you'd be comfortable with that in a forum though. Is there a way you could delete the post after you receive the email from me?

The files are too big to upload on here

[rpeare]

the database doesn't need to be anything but the table that you're importing to and the form you're using as the interface, the rest of it is useless for the purposes of this question so you can just create a new database and import only those items necessary for the form to function correctly.

After that you can zip up the database and the excel file to compress them.

I've PM'd my email address to you

[ItsMe]

FYI

I was able to reproduce the F1 error. I belive it has to do with multiple tabs in a workbook vs. single tab/worksheet.

I was able to get the following to work with XLS. will test xlsx next.

Dim strTable As String
Dim strFilePath As String

With Application.FileDialog(msoFileDialogFilePicker)

    .AllowMultiSelect = False
    .Title = "Locate a Excel file to Import"
    .ButtonName = "Import"
    .Filters.Clear
'    .Filters.Add "Excel", "*.xlsx"
    .Filters.Add "Excel", "*.xls"
    .InitialFileName = "C:\Test"
    .InitialView = msoFileDialogViewThumbnail
        If .Show = 0 Then
        'There is a problem
        Exit Sub
        End If
    'Save the first file selected
    strFilePath = Trim(.SelectedItems(1))
End With

'strTable = "tbl1_PartPricingDataAggregation"
strTable = "tblTest"

'DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, strTable, strFilePath, False, "A1:L1000"
DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel8, strTable, strFilePath, True

[ItsMe]

OK, two things. I do not see that the TransferSpreadsheet method supports xlsx. The latest version seems to be Office 12 which should be 2007 but I keep getting errors trying to export as xlsx. So I will guess this is where the problem lies.

The second thing is that I provided the wrong Constant. Rather than acSpreadsheetTypeExcel12Xml should use acSpreadsheetTypeExcel12 or acSpreadsheetTypeExcel9

[Ace2014]

Ok wow, great, so the template excel needs to be in .xls?

Thanks so much, I'm going to try it out now!!

[Ace2014]

Is there supposed to be a " ' " before the 2nd to last line of code you provided?
'DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12Xml, strTable, strFilePath, False, "A1:L1000"

[rpeare]

If the problem is multiple worksheets you probably want to modify your range

"A1:L1000"

To indicate the worksheet your range is referring to, the code will automatically assume you are importing from the first worksheet unless otherwise stated in your code.

Thanks itsme! I didn't even think about wondering if it had multiple worksheets.

[Ace2014]

...Funny that you mention that, since I'll have to duplicate this macro for all of the following tabs...hence why they're all in the same Excel
How do you specify the spreadsheet in the code?

[ItsMe]

Yah I did not extensively test importing XLSX. So the issue may, in fact, be "Range". I stopped testing at not being able to execute the following.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tblTest", "C:\Test\TestExport.xlsx", False

It would error "Wrong Extension Type"

[Ace2014]

IT WORKED!!! You're brilliant!

[Ace2014]

Well, I saved my file as Excel as .xls and it appended to my table/the table you created.

But how can I specify the spreadsheet?

[ItsMe]

Post #54 has the answer to that.

[Ace2014]

Yeah, I don't know how to specify the spreadsheet though in code. I found this
http://stackoverflow.com/questions/7...cess-using-vba

I tried adapting my VBA myself, but I'm getting an error.

Sub Import()Dim strTable As String
Dim strFilePath As String


With Application.FileDialog(msoFileDialogFilePicker)


    .AllowMultiSelect = False
    .Title = "Locate a Excel file to Import"
    .ButtonName = "Import"
    .Filters.Clear
'    .Filters.Add "Excel", "*.xlsx"
    .Filters.Add "Excel", "*.xls"
    .InitialFileName = "C:\Test"
    .InitialView = msoFileDialogViewThumbnail
        If .Show = 0 Then
        'There is a problem
        Exit Sub
        End If
    'Save the first file selected
    strFilePath = Trim(.SelectedItems(1))
End With


strTable = "tbl1_PartPricingDataAggregation"
'strTable = "tblTest"


'DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12, strTable, strFilePath, False, "A1:L1000"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strFilePath& '.tbl1a_Upload_Market_Data!', True,
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tbl1_PartPricingDataAggregation", "C:\Test\TestExport.xlsx", False


End Sub