Triple Import Process from Excel to Access

[Rob]

Attached Below is the Code that i have created.
Private Sub Command14_Click()
' 1 Run FileConversion.vbs script
' 2 Open Partmstr.xlsm run macro impt, then save and close
' 3 Import partmstr file from the AWlabels file to the AW-Saleable table

Const OverwriteExisting = True
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CopyFile "\\file\All\Labels\partmstr.dat",
"\\file\Databases\LABELROOM\PARTMSTR.TXT", OverwriteExisting

Dim oApp As Object

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open FileName:="\\file\Databases\LABELROOM\Partmstr.xlsm"
oApp.Visible = False
oApp.UserControl = True

oApp.Quit

'have an error going on about text greater than 255 need a resume next cmd
DoCmd.TransferSpreadsheet acImportDelim, , "AW-Saleable",
"\\file\Databases\LABELROOM\partmstr.XLSM", False

End Sub

First issue is i have is i get an error about text greater than 255 (from
Excel to Access 07)
The Second issue is i would like to just import the first 9 colums of the
data in the Excel sheet to the AW-saleable table, and either before or after
the import change the fourth colum in the AW-Saleable table from text to memo

The Excel file is updated on a daily basis...any ideas how to help.

 

[Jeanette Cunningham]

Hi Rob,

Here are some suggestions to get you started.

The 255 error is probably the about the maximum number of characters allowed
in a text field in an access table. Can you import to a memo field instead.
(Note: I haven't tested importing to a memo field!)

To just import the first 9 columns and change field type from text to memo
is more easily done with the use of a staging table.
The staging table imports all the fields from excel.
From the staging table you use a series of queries to get the just the data
you want (the first 9 columsn) into the final table that will hold the
imported data.

Make sure the final table has a memo field. Instead of trying to change a
text field to a memo field while you import, use a query to add the data in
the text field in your staging table to the memo field in your final table.

Post back if there are still problems.

Jeanette Cunningham

 

[Rob]

Jeanette,

Thank you for your input, i have managed to import the file selecting only
the colums that i needed, (there was only one colum that had to be changed
from text to a memo field) I appreciated your time into looking into this.
thank you.