0

How does one import a xlsm file into Access?

Naturally, Access gives me the "Please check that the file exists and is in the correct format" error. How ought I to progress?

Working in 2010 for both Excel and Access.

jph
  • 13
  • 1
  • 3
  • If you are concerned about importing the file once, you could simply change its extension to .xlsx – parakmiakos Mar 12 '14 at 15:01
  • My apologies. Bad question lacking information. This is something that will need to happen ~ 30 times/week. Therefore, hoping to write a macro or something analagous. – jph Mar 12 '14 at 15:10

3 Answers3

2

Here's some code, you'll have to change it for your specific filename and such:

Sub testimport()
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "ttblTempAppend1", _
      "M:\Quality\Measurement & Evaluation\Projects\VHP\Dashboard\AAA_Rupture\ttblTempAppend1.xlsm", _
      True, "ttblTempAppend1"
End Sub
Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • Thanks for the code! I tried it out by replacing the file location, but it is not working. Must I replace other things? ("ttblTempAppend1" ?) – jph Mar 12 '14 at 15:15
  • Yes. That's the name of the table you're importing the data to. Have a look at http://msdn.microsoft.com/en-us/library/office/ff844793(v=office.15).aspx to see all the required inputs. – Johnny Bones Mar 12 '14 at 15:25
  • Does the "acSpreadsheetTypeExcel12Xml" also apply to 2010 xlsm files? Does it apply to all Excel 2010 file versions? – jph Mar 12 '14 at 16:13
1

I'm beginning a project where this will become an issue as well. I think it will be more practical to use the macro-enabled file as a control console rather than the import source. This is the first step towards that concept:

Sub Import_Ready()

'This will take the active sheet, copy it, prepare it for import, and store it in the same directory
'as your source file. You will need to change the coding to reference a different sheet if you want
'to make this into a button or part of a process. (Or just activate the sheet you want at the start)
'The steps will be explained as though you are an evil Wizard...

    'Create Some obedient variables
    Dim ThisDirectory As String
    Dim DocumentName As String
    Dim StartingSheet As String
    Dim StartingPoint As Range

    'Reveal to those variables the nature of their purpose on this earth
    ThisDirectory = ActiveWorkbook.Path
    DocumentName = "Import Ready.xlsx"
    StartingSheet = ActiveSheet.Name
    Set StartingPoint = Selection

    'Hide the macro magic from those curious savages and ignore the ethical ramifications of what you're about to do
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    'Copy the active sheet. Now kill the living formulas and replace them with undead values that never change
    ActiveSheet.Cells.Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = DocumentName
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    'Take that brand new sheet and turn it into it's very own file, free from the burden of macro-enabled freedom, then put it away
    Sheets(DocumentName).Move
    ActiveWorkbook.SaveAs Filename:=ThisDirectory & "\" & DocumentName _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close

    'Go back to where you started this grand journey
    Sheets(StartingSheet).Select
    StartingPoint.Select

    'You're done! turn warnings and screen movement back on and pretend like nothing happened...
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

I'm sorry my code snippet doesn't look all colorful. I haven't figured out how to do that yet. (This is my second post to StackOverflow)

anəˈnimədē
  • 113
  • 2
  • 11
  • 1
    Hi @anəˈnimədē, are you asking a question, or answering the original question? – Wai Ha Lee Apr 30 '15 at 20:39
  • Mainly the latter. I was relating to the OP and suggesting a possible solution (that worked for my needs). Namely, use the .xlsm to create a .xlsx file in the same directory. I'm pretty sure this would work great for jph as well. (I'm assuming your initials stand for "Jazz Player Hottie," right?) It would be especially handy to call the sub before close if it's done regularly. – anəˈnimədē May 12 '15 at 00:31
  • Wondering how you could adapt your code in combination with code to import multiple sheets at once as separate tables, e.g. https://www.mrexcel.com/board/threads/importing-multiple-sheets-as-separate-tables-in-access.569905/ – spops Apr 07 '21 at 20:23
0

If you are using Macro Builder, just add .xlsm at the end of your excel file name in the "file name" directory

For example:

File name: C:\Database\Sales\Excel_Access_DataImport.xlsm

Andres
  • 1
  • 1