Triple Import Process from Excel to Access

  • Thread starter Thread starter Rob
  • Start date Start date
R

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.
 
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
 
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.
 
Back
Top