Routing From Excel to .txt before Import

[Dugantrain]

Hi, all. What I'm looking to do is to entirely control in Access the transfer of an Excel Worksheet from Excel to a .txt file, and then from that Excel file to Access. The second step is easy, just use TransferText and its specifications. Now, if someone could direct me to the first step, transferring from Excel to .txt from within Access, it'd be much appreciated.

 

[FoFa]

Well you have to control it from Access via Excel (send excel commands to excel to do it). Or you can can import the spread sheet to Access than export the text file, than import the text file (DO I HAVE TO SAY ANYTHING ABOUT THAT?!?) From what you are saying it sounds like you are trying to export a text file from excel, than import it to Access. That beggs the question, why? I am going out on a limb here to assume it is the age old problem of data type problems coming from Excel. If that is the case, I have found it is better to import the data into Access, than use queries to move the data to a properly defined table doing data type conversion in the process. Maybe you could clearify your situation a little more?

 

[Dugantrain]

Yes, the latter is exactly it; bad data types. I have a raw import table with all of the data types specified as Text which, for now, we're copying and pasting into. After the copy-paste, I have a large, unholy module which correctly performs the clean-up on the import table and moves it all over into another table (originally, I wrote 8 or 10 queries to do it and called those queries in a command button's On_Click, but the one function is lightning quick; taught me a lot about ADO, too). There have been so many problems with trying to do a simple TransferSpreadsheet (Conversion Failures, Columns get added/deleted daily, Column Names are unuseable as Access Column Headings, etc. etc.) that I was going to try to get it into .txt format first and then go from there. Another idea I had was to use MS Query to transfer only the columns that I need to another worksheet and then import that new worksheet into Access, but much of the data was either being incorrectly converted or lost completely when going from the original Worksheet to the "clean" Worksheet. So TransferText is my next effort.

 

[Philem]

Hullo. Alrighty, after being redirected here for another board, I understand what you are trying to do. The good news, YES you are in fact able to do this! It's not even really all that hard. I've got an Access db that does all sorts of hijinx with Excel.

The basic idea you need is calling on Excel VBA from within Access. Take a look at this PSEUDOCODE:

Option Exlicit
Option Compare Database

Dim objXL as Excel.Application

Set objXL = CreateObject("Excel.Application")

With objXL
   'Anything you want here, up to and including duplicates of FULL Excel macros
End With
End Sub

It's quite easy, once you know what you need to get into Excel's VBA set. I'd making a macro in Excel to do the dump, then calling upon that exact macro from Access. Check out www.mvps.org/access for some additional information on this.

HTH

P

 

[Dugantrain]

Excellent, thanks so much!