Using Excel Macro to Import Data to Access

L

learningaccess

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
 
M

MacDermott

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
 
L

learningaccess

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.
 
M

MacDermott

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
 
L

learningaccess

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
 
G

George Nicholson

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
Click to expand...
Click to expand...
 
L

learningaccess

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...
Click to expand...
 
G

George Nicholson

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...
Click to expand...
 
L

learningaccess

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...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Importing from Excel using VB with questions 1
display warning message 1
running Access procedure from Excel 5
I need some help with VBA and macros 1
could someone look at my code please and tell me what's wrong? tha 1
Is there a better way? 2
compact Access DB from Excel 1
The data in a linked Excel spreadsheet column is truncated to 255 characters in an Access database 0

Top