Using Excel Macro to Import Data to Access

learningaccess · Dec 13, 2007

I am using the code below to try to import data from Excel to Access but keep
getting: "Run-Time error '3274' External Table is not in the expected
format"

VBcode:

'Set Access as Application
Dim appAcc As Access.Application

'Opens Access or gets reference to app already running
Set appAcc = New Access.Application

'Optional to show or hide Access
appAcc.Visible = True

appAcc.OpenCurrentDatabase "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\Windows Inventory.mdb"

'For running the TransferSpreadsheet command
appAcc.DoCmd.TransferSpreadsheet acImport, , "ScanForm", "C:\Documents
and Settings\hdavis.BROWNPRECISION\Desktop\scans.xls"

'Close Access
appAcc.Quit

MacDermott · Dec 14, 2007

You haven't said what version of Excel your spreadsheet is in. If it's
older than Excel8, you'll need to specify that in the second parameter.
I also notice that you've given ScanForm as the name of the table you're
importing into -
is that really the name of a *table*?

HTH
- Turtle

learningaccess · Dec 14, 2007

I am actually using the same Excel macro to delimit a .txt file and save that
as the "scans.xls" file. I am using Excel 2003 to do this. ScanForm is the
name of the table in the Database that i would like to import the information
to.

MacDermott · Dec 14, 2007

Hmmm

Could we see the code you're using to create the scans.xls file?
Have you considered using TransferText to import the text file into Access,
bypassing Excel?

HTH
- Turtle

learningaccess · Dec 14, 2007

Below is part of the macro i am using. There is alot more formatting of the
data in the whole macro but that would have taken up too much space.

Thanks for your help!

Sub FormatData()
'
' FormatData Macro
' Macro recorded 4/19/2007 by Heath Davis
'
' Keyboard Shortcut: Ctrl+Shift+Q

ChDir "C:\Documents and Settings\hdavis.BROWNPRECISION\Desktop"
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\hdavis.BROWNPRECISION\Desktop\scans.txt",
Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 2), _
TrailingMinusNumbers:=True

'Deletes leading rows 1
Rows("1:2").Select
Selection.Delete Shift:=xlUp

'Modifies first record 2
Range("A2").Select
Selection.Cut
Range("B1").Select
ActiveSheet.Paste

Range("A5").Select
Selection.Cut
Range("C1").Select
ActiveSheet.Paste

Range("D1").Select
ActiveCell = "=Concatenate(B1, C1)"

Range("D1").Select
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues

ActiveWorkbook.Save
ChDir "C:\Documents and Settings\hdavis.BROWNPRECISION\Desktop"
ActiveWorkbook.Close SaveChanges:=True

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\hdavis.BROWNPRECISION\Desktop\scans.txt",
Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 3), Array(2,
2)), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").Select
Columns("B:B").EntireColumn.AutoFit
Columns("A:A").Select
Selection.NumberFormat = "[$-409]m/d/yy h:mm:ss AM/PM;@"
Range("A1").Select
ActiveWorkbook.SaveAs "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\scans.xls", 9
ActiveWorkbook.Close SaveChanges:=True

'Set Access as Application
Dim appAcc As Access.Application

'Opens Access or gets reference to app already running
Set appAcc = New Access.Application

'Optional to show or hide Access
appAcc.Visible = True

appAcc.OpenCurrentDatabase "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\Windows Inventory.mdb"

'For running the TransferSpreadsheet command
appAcc.DoCmd.TransferSpreadsheet acImport, , "ScanForm", "C:\Documents
and Settings\hdavis.BROWNPRECISION\Desktop\scans.xls"

'Close Access
appAcc.Quit
End Sub

George Nicholson · Dec 14, 2007

ActiveWorkbook.SaveAs "C:\Documents and

Settings\hdavis.BROWNPRECISION\Desktop\scans.xls", 9
ActiveWorkbook.Close SaveChanges:=True

Per the ObjectBrowser for xlFileFormat constants, 9 = DIF format. Not sure
what DIF is but I'd bet that it explains the "not in expected (excel)
format" message: you are saving the file in the wrong format.

Try using xlWorkbookNormal rather than 9.

--
HTH,
George

learningaccess said:
Below is part of the macro i am using. There is alot more formatting of
the
data in the whole macro but that would have taken up too much space.

Thanks for your help!

Sub FormatData()
'
' FormatData Macro
' Macro recorded 4/19/2007 by Heath Davis
'
' Keyboard Shortcut: Ctrl+Shift+Q

ChDir "C:\Documents and Settings\hdavis.BROWNPRECISION\Desktop"
Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\scans.txt",
Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 2), _
TrailingMinusNumbers:=True

'Deletes leading rows 1
Rows("1:2").Select
Selection.Delete Shift:=xlUp

'Modifies first record 2
Range("A2").Select
Selection.Cut
Range("B1").Select
ActiveSheet.Paste

Range("A5").Select
Selection.Cut
Range("C1").Select
ActiveSheet.Paste

Range("D1").Select
ActiveCell = "=Concatenate(B1, C1)"

Range("D1").Select
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues

ActiveWorkbook.Save
ChDir "C:\Documents and Settings\hdavis.BROWNPRECISION\Desktop"
ActiveWorkbook.Close SaveChanges:=True

Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\scans.txt",
Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 3),
Array(2,
2)), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").Select
Columns("B:B").EntireColumn.AutoFit
Columns("A:A").Select
Selection.NumberFormat = "[$-409]m/d/yy h:mm:ss AM/PM;@"
Range("A1").Select
ActiveWorkbook.SaveAs "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\scans.xls", 9
ActiveWorkbook.Close SaveChanges:=True

'Set Access as Application
Dim appAcc As Access.Application

'Opens Access or gets reference to app already running
Set appAcc = New Access.Application

'Optional to show or hide Access
appAcc.Visible = True

appAcc.OpenCurrentDatabase "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\Windows Inventory.mdb"

'For running the TransferSpreadsheet command
appAcc.DoCmd.TransferSpreadsheet acImport, , "ScanForm", "C:\Documents
and Settings\hdavis.BROWNPRECISION\Desktop\scans.xls"

'Close Access
appAcc.Quit
End Sub

MacDermott said:

Hmmm

Could we see the code you're using to create the scans.xls file?
Have you considered using TransferText to import the text file into
Access,
bypassing Excel?

HTH
- Turtle

message

Click to expand...

learningaccess · Dec 14, 2007

That got rid of the "unexpected format" error but now it says, "Run-time
error '2391'. Field 'F1' doesn't exist in destination table 'ScanForm'."

George Nicholson said:
ActiveWorkbook.SaveAs "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\scans.xls", 9
ActiveWorkbook.Close SaveChanges:=True

Click to expand...

Per the ObjectBrowser for xlFileFormat constants, 9 = DIF format. Not sure
what DIF is but I'd bet that it explains the "not in expected (excel)
format" message: you are saving the file in the wrong format.

Try using xlWorkbookNormal rather than 9.

--
HTH,
George

learningaccess said:

Below is part of the macro i am using. There is alot more formatting of
the
data in the whole macro but that would have taken up too much space.

Thanks for your help!

Sub FormatData()
'
' FormatData Macro
' Macro recorded 4/19/2007 by Heath Davis
'
' Keyboard Shortcut: Ctrl+Shift+Q

ChDir "C:\Documents and Settings\hdavis.BROWNPRECISION\Desktop"
Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\scans.txt",
Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 2), _
TrailingMinusNumbers:=True

'Deletes leading rows 1
Rows("1:2").Select
Selection.Delete Shift:=xlUp

'Modifies first record 2
Range("A2").Select
Selection.Cut
Range("B1").Select
ActiveSheet.Paste

Range("A5").Select
Selection.Cut
Range("C1").Select
ActiveSheet.Paste

Range("D1").Select
ActiveCell = "=Concatenate(B1, C1)"

Range("D1").Select
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues

ActiveWorkbook.Save
ChDir "C:\Documents and Settings\hdavis.BROWNPRECISION\Desktop"
ActiveWorkbook.Close SaveChanges:=True

Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\scans.txt",
Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 3),
Array(2,
2)), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").Select
Columns("B:B").EntireColumn.AutoFit
Columns("A:A").Select
Selection.NumberFormat = "[$-409]m/d/yy h:mm:ss AM/PM;@"
Range("A1").Select
ActiveWorkbook.SaveAs "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\scans.xls", 9
ActiveWorkbook.Close SaveChanges:=True

'Set Access as Application
Dim appAcc As Access.Application

'Opens Access or gets reference to app already running
Set appAcc = New Access.Application

'Optional to show or hide Access
appAcc.Visible = True

appAcc.OpenCurrentDatabase "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\Windows Inventory.mdb"

'For running the TransferSpreadsheet command
appAcc.DoCmd.TransferSpreadsheet acImport, , "ScanForm", "C:\Documents
and Settings\hdavis.BROWNPRECISION\Desktop\scans.xls"

'Close Access
appAcc.Quit
End Sub

MacDermott said:

Hmmm

Could we see the code you're using to create the scans.xls file?
Have you considered using TransferText to import the text file into
Access,
bypassing Excel?

HTH
- Turtle

message
I am actually using the same Excel macro to delimit a .txt file and save
that
as the "scans.xls" file. I am using Excel 2003 to do this. ScanForm is
the
name of the table in the Database that i would like to import the
information
to.

:

You haven't said what version of Excel your spreadsheet is in. If
it's
older than Excel8, you'll need to specify that in the second
parameter.
I also notice that you've given ScanForm as the name of the table
you're
importing into -
is that really the name of a *table*?

HTH
- Turtle

message
I am using the code below to try to import data from Excel to Access
but
keep
getting: "Run-Time error '3274' External Table is not in the
expected
format"

VBcode:

'Set Access as Application
Dim appAcc As Access.Application

'Opens Access or gets reference to app already running
Set appAcc = New Access.Application

'Optional to show or hide Access
appAcc.Visible = True

appAcc.OpenCurrentDatabase "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\Windows Inventory.mdb"

'For running the TransferSpreadsheet command
appAcc.DoCmd.TransferSpreadsheet acImport, , "ScanForm",
"C:\Documents
and Settings\hdavis.BROWNPRECISION\Desktop\scans.xls"

'Close Access
appAcc.Quit

Click to expand...

Click to expand...

George Nicholson · Dec 14, 2007

Try:
appAcc.DoCmd.TransferSpreadsheet acImport, , "ScanForm", "C:\Documents
and Settings\hdavis.BROWNPRECISION\Desktop\scans.xls",-1

The -1 for "HasFieldNames" argument tells Access to use the first row of
data as field names rather than the automaticaly generated F1, F2, F3, F4...

This assumes that the file you saved has column labels, rather than data, in
the first row. It also assumes that those column names *exactly match* the
field names in ScanForm.

If those 2 conditions aren't true, we'll need more specifics as to what
exactly is in the file you are importing, what is the structure of ScanForm
and how you expect Access to fit the 2 together.

Keep in mind that neither we nor Access are psyhic. :-)

--
HTH,
George

learningaccess said:
That got rid of the "unexpected format" error but now it says, "Run-time
error '2391'. Field 'F1' doesn't exist in destination table 'ScanForm'."

George Nicholson said:

ActiveWorkbook.SaveAs "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\scans.xls", 9
ActiveWorkbook.Close SaveChanges:=True

Click to expand...

Per the ObjectBrowser for xlFileFormat constants, 9 = DIF format. Not
sure
what DIF is but I'd bet that it explains the "not in expected (excel)
format" message: you are saving the file in the wrong format.

Try using xlWorkbookNormal rather than 9.

--
HTH,
George

message

Below is part of the macro i am using. There is alot more formatting of
the
data in the whole macro but that would have taken up too much space.

Thanks for your help!

Sub FormatData()
'
' FormatData Macro
' Macro recorded 4/19/2007 by Heath Davis
'
' Keyboard Shortcut: Ctrl+Shift+Q

ChDir "C:\Documents and Settings\hdavis.BROWNPRECISION\Desktop"
Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\scans.txt",
Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 2), _
TrailingMinusNumbers:=True

'Deletes leading rows 1
Rows("1:2").Select
Selection.Delete Shift:=xlUp

'Modifies first record 2
Range("A2").Select
Selection.Cut
Range("B1").Select
ActiveSheet.Paste

Range("A5").Select
Selection.Cut
Range("C1").Select
ActiveSheet.Paste

Range("D1").Select
ActiveCell = "=Concatenate(B1, C1)"

Range("D1").Select
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues

ActiveWorkbook.Save
ChDir "C:\Documents and Settings\hdavis.BROWNPRECISION\Desktop"
ActiveWorkbook.Close SaveChanges:=True

Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\scans.txt",
Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 3),
Array(2,
2)), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").Select
Columns("B:B").EntireColumn.AutoFit
Columns("A:A").Select
Selection.NumberFormat = "[$-409]m/d/yy h:mm:ss AM/PM;@"
Range("A1").Select
ActiveWorkbook.SaveAs "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\scans.xls", 9
ActiveWorkbook.Close SaveChanges:=True

'Set Access as Application
Dim appAcc As Access.Application

'Opens Access or gets reference to app already running
Set appAcc = New Access.Application

'Optional to show or hide Access
appAcc.Visible = True

appAcc.OpenCurrentDatabase "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\Windows Inventory.mdb"

'For running the TransferSpreadsheet command
appAcc.DoCmd.TransferSpreadsheet acImport, , "ScanForm",
"C:\Documents
and Settings\hdavis.BROWNPRECISION\Desktop\scans.xls"

'Close Access
appAcc.Quit
End Sub

:

Hmmm

Could we see the code you're using to create the scans.xls file?
Have you considered using TransferText to import the text file into
Access,
bypassing Excel?

HTH
- Turtle

message
I am actually using the same Excel macro to delimit a .txt file and
save
that
as the "scans.xls" file. I am using Excel 2003 to do this. ScanForm
is
the
name of the table in the Database that i would like to import the
information
to.

:

You haven't said what version of Excel your spreadsheet is in. If
it's
older than Excel8, you'll need to specify that in the second
parameter.
I also notice that you've given ScanForm as the name of the table
you're
importing into -
is that really the name of a *table*?

HTH
- Turtle

in
message
I am using the code below to try to import data from Excel to
Access
but
keep
getting: "Run-Time error '3274' External Table is not in the
expected
format"

VBcode:

'Set Access as Application
Dim appAcc As Access.Application

'Opens Access or gets reference to app already running
Set appAcc = New Access.Application

'Optional to show or hide Access
appAcc.Visible = True

appAcc.OpenCurrentDatabase "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\Windows Inventory.mdb"

'For running the TransferSpreadsheet command
appAcc.DoCmd.TransferSpreadsheet acImport, , "ScanForm",
"C:\Documents
and Settings\hdavis.BROWNPRECISION\Desktop\scans.xls"

'Close Access
appAcc.Quit

Click to expand...

Click to expand...

learningaccess · Dec 17, 2007

You sir, are a GENIUS!!

Thank you! I did not realize that i had to have column labels in my excel
sheet to match the table in access. I added some code to automatically add
the column labels and it worked like a charm.

Thanks again!

George Nicholson said:
Try:
appAcc.DoCmd.TransferSpreadsheet acImport, , "ScanForm", "C:\Documents
and Settings\hdavis.BROWNPRECISION\Desktop\scans.xls",-1

The -1 for "HasFieldNames" argument tells Access to use the first row of
data as field names rather than the automaticaly generated F1, F2, F3, F4...

This assumes that the file you saved has column labels, rather than data, in
the first row. It also assumes that those column names *exactly match* the
field names in ScanForm.

If those 2 conditions aren't true, we'll need more specifics as to what
exactly is in the file you are importing, what is the structure of ScanForm
and how you expect Access to fit the 2 together.

Keep in mind that neither we nor Access are psyhic.

--
HTH,
George

learningaccess said:

That got rid of the "unexpected format" error but now it says, "Run-time
error '2391'. Field 'F1' doesn't exist in destination table 'ScanForm'."

George Nicholson said:

ActiveWorkbook.SaveAs "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\scans.xls", 9
ActiveWorkbook.Close SaveChanges:=True

Per the ObjectBrowser for xlFileFormat constants, 9 = DIF format. Not
sure
what DIF is but I'd bet that it explains the "not in expected (excel)
format" message: you are saving the file in the wrong format.

Try using xlWorkbookNormal rather than 9.

--
HTH,
George

message
Below is part of the macro i am using. There is alot more formatting of
the
data in the whole macro but that would have taken up too much space.

Thanks for your help!

Sub FormatData()
'
' FormatData Macro
' Macro recorded 4/19/2007 by Heath Davis
'
' Keyboard Shortcut: Ctrl+Shift+Q

ChDir "C:\Documents and Settings\hdavis.BROWNPRECISION\Desktop"
Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\scans.txt",
Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 2), _
TrailingMinusNumbers:=True

'Deletes leading rows 1
Rows("1:2").Select
Selection.Delete Shift:=xlUp

'Modifies first record 2
Range("A2").Select
Selection.Cut
Range("B1").Select
ActiveSheet.Paste

Range("A5").Select
Selection.Cut
Range("C1").Select
ActiveSheet.Paste

Range("D1").Select
ActiveCell = "=Concatenate(B1, C1)"

Range("D1").Select
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues

ActiveWorkbook.Save
ChDir "C:\Documents and Settings\hdavis.BROWNPRECISION\Desktop"
ActiveWorkbook.Close SaveChanges:=True

Workbooks.OpenText Filename:= _
"C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\scans.txt",
Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 3),
Array(2,
2)), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").Select
Columns("B:B").EntireColumn.AutoFit
Columns("A:A").Select
Selection.NumberFormat = "[$-409]m/d/yy h:mm:ss AM/PM;@"
Range("A1").Select
ActiveWorkbook.SaveAs "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\scans.xls", 9
ActiveWorkbook.Close SaveChanges:=True

'Set Access as Application
Dim appAcc As Access.Application

'Opens Access or gets reference to app already running
Set appAcc = New Access.Application

'Optional to show or hide Access
appAcc.Visible = True

appAcc.OpenCurrentDatabase "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\Windows Inventory.mdb"

'For running the TransferSpreadsheet command
appAcc.DoCmd.TransferSpreadsheet acImport, , "ScanForm",
"C:\Documents
and Settings\hdavis.BROWNPRECISION\Desktop\scans.xls"

'Close Access
appAcc.Quit
End Sub

:

Hmmm

Could we see the code you're using to create the scans.xls file?
Have you considered using TransferText to import the text file into
Access,
bypassing Excel?

HTH
- Turtle

message
I am actually using the same Excel macro to delimit a .txt file and
save
that
as the "scans.xls" file. I am using Excel 2003 to do this. ScanForm
is
the
name of the table in the Database that i would like to import the
information
to.

:

You haven't said what version of Excel your spreadsheet is in. If
it's
older than Excel8, you'll need to specify that in the second
parameter.
I also notice that you've given ScanForm as the name of the table
you're
importing into -
is that really the name of a *table*?

HTH
- Turtle

in
message
I am using the code below to try to import data from Excel to
Access
but
keep
getting: "Run-Time error '3274' External Table is not in the
expected
format"

VBcode:

'Set Access as Application
Dim appAcc As Access.Application

'Opens Access or gets reference to app already running
Set appAcc = New Access.Application

'Optional to show or hide Access
appAcc.Visible = True

appAcc.OpenCurrentDatabase "C:\Documents and
Settings\hdavis.BROWNPRECISION\Desktop\Windows Inventory.mdb"

'For running the TransferSpreadsheet command
appAcc.DoCmd.TransferSpreadsheet acImport, , "ScanForm",
"C:\Documents
and Settings\hdavis.BROWNPRECISION\Desktop\scans.xls"

'Close Access
appAcc.Quit

Click to expand...

Click to expand...