Using docmd.transferspreadsheet

[GoodyGoody]

Hi, I want to import an Excel SS containing 2 columns: RacerunnerNumber and Racetime. I'm allowing the user to select Excel files from 2003 onwards. However, when using the transferspreadsheet method it requires you to specify a SS type. Is there a way of me knowing which SS type it is when I import the object?

    Dim xlApp As Object
    Dim xlWb As Object
    Dim xlWs As Object

    Set xlApp = CreateObject("Excel.Application") 'the excel application
    strFileName = .SelectedItems(1) 'picking the selected file full path
    Set xlWb = xlApp.Workbooks.Open(strFileName, True, False)
    Set xlWs = xlApp.Worksheets(1)

Thanks in advance as ever

 

[vba_php]

that code looks like what I gave you in your last thread. here's what you need: https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet , which leads you to: https://docs.microsoft.com/en-us/office/vba/api/access.acspreadsheettype

 

[GoodyGoody]

that code looks like what I gave you in your last thread. here's what you need: https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet , which leads you to: https://docs.microsoft.com/en-us/office/vba/api/access.acspreadsheettype

Thanks Adam. It came from elsewhere but I guess it's fairly standard naming conventions. Actually, been to these 2 threads but I can't see a solution anywhere which enables me to interrogate the selected file for it's Excel version so I can use the right option on the transferspreadsheet command. Or am I missing something? Probably!

 

[vba_php]

Actually, been to these 2 threads but I can't see a solution anywhere which enables me to interrogate the selected file for it's Excel version so I can use the right option on the transferspreadsheet command.

*interrogate*? what, are we working for the NSA or CIA now? the *interrogation* you speak of can be done simply by looking at the file extension and selecting the appropriate [number] as the last byte/s of the SpreadSheetType argument in that function. that link I gave you should also give an explanation of what file extensions belong to what spread sheet type. did you see that? as far as being concerned over the excel application "version", that's kind of irrelevant because virtually every excel version after 2007 has been able to open all file types sourcing all the way back to the birth of excel (I think). e.g. - xls, xlsx, xlsm, xml, etc...

 

[GinaWhipp]

Hi, I want to import an Excel SS containing 2 columns: RacerunnerNumber and Racetime. I'm allowing the user to select Excel files from 2003 onwards. However, when using the transferspreadsheet method it requires you to specify a SS type. Is there a way of me knowing which SS type it is when I import the object?

    Dim xlApp As Object
    Dim xlWb As Object
    Dim xlWs As Object

    Set xlApp = CreateObject("Excel.Application") 'the excel application
    strFileName = .SelectedItems(1) 'picking the selected file full path
    Set xlWb = xlApp.Workbooks.Open(strFileName, True, False)
    Set xlWs = xlApp.Worksheets(1)

Thanks in advance as ever

This is correct when using the TransferSpreadsheet method. A way to give Users and option can be done by using Excel Automation to which I see you have a start and letting your Users pick the file by using FileDialog. to select the file.

Application.FileDialog property (Access)

Office VBA reference topic

docs.microsoft.com

Then you can not only control the extensions but allow XLS and XSLX.

 

[Gasman]

Open the file
Use activeworkbook.FileFormat
Then compare to https://docs.microsoft.com/en-us/pr...007/bb241279(v=office.12)?redirectedfrom=MSDN

HTH

 

[GoodyGoody]

Thanks Gasman. That was what I was looking for. I know there is an argument for the transferspreadsheet method but you need to know what format of Excel it is in for it to work and this would seem to be the answer I was looking for. Thanks again