Missing data when Import??

[Maxine1010]

Hi,

I need a point in the right direction, if anyone can help.

I am importing data from Excel via a command button to an Access table. The majority of the data transfers fine except the last 2 columns are missing.

These columns generally do not contain much data and so I think this is the reason for them being dismissed. I have added text to some of the cells and then transferred the data and this works but otherwise Access doesn't want to know.

I also have the added problem that the import process is performed via a command button with an event procedure reading:

Private Sub Command66_Click()
prompt$ = "YOU ARE ABOUT TO IMPORT DATA - DO YOU WISH TO PROCEED?"
response = MsgBox(prompt$, vbYesNo, "Data Import")
If response = vbNo Then Exit Sub
datevar = Date - 1
Call import(datevar)
MsgBox "FILES IMPORTED"
End Sub

I don't know much VB at all so I don't even know where to start if the problem lies within this.

Any help would be much appreciated! Thanks

 

[pono1]

M,

Perhaps you've already done this...

Create a new Access file and try to manually import the Excel spreadsheet into a table: (in A2K: click on File, click on Get External Data, click on Import, etc).

If you can get all columns to import OK in your new Access file, then focus on the import code as the source of your problem.
(Either re-create the code or look for a function named Import and slog through the details.)

Otherwise (if you can't get the Excel columns to import into a clean Access file), then re-visit the spreadsheet for problems.

Regards,
Tim

 

[antomack]

If the import is using the TransferSpreadsheet method then it may be that there is a range specified for the import which does not inlcude the last two columns. Changing this range to include the two columns or removing the range altogether should bring the two columns through. Check the Help file for 'TransferSpreadsheet'

As Tim said if the import works manually and brings in the two fields then it's in the import code that's dropping the two columns.

 

[The_Doc_Man]

Since the code that was posted appears to call a subroutine, I'll bet that the subroutine names an Import specification. That specification will indicate the columns to be imported. And it might be that the specification pre-dates the addition of the last two columns. OR, someone just forgot to include them.