Brad Marks
BradM at blackforestltd.com
Thu Jun 23 07:53:36 CDT 2011
Mark, I have a lot less experience than others here in AccessD. I have exported files from Access to Excel and imported Excel files into Access. However, I have not yet experimented with initiating Excel directly from Access. Could you post the code on how to do this or perhaps point me to a website that spells this out. I would like to learn more about this and it would be nice to have an example to start from. Thanks, Brad ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~ -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms Sent: Wednesday, June 22, 2011 6:01 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Automating Excel imports Just initiate an Excel instance from within Access, Open the workbook, determine the sheet, get the last used row, determine the zip code column and starting row number, and then iterate thru each cell placing a single quote in the cell: For i = RowStart to LastRow oSht.Cells(I,ZipColNum).value = "'" & oSht.Cells(I, ZipColnum).Value Next There is no way to do a mass i.e. range-based conversion from numbers to text. You can do a mass conversion from text to numbers however. > -----Original Message----- > From: accessd-bounces at databaseadvisors.com [mailto:accessd- > bounces at databaseadvisors.com] On Behalf Of Darrell Burns > Sent: Wednesday, June 22, 2011 6:28 PM > To: 'Access Developers discussion and problem solving' > Subject: [AccessD] Automating Excel imports > > My client needs to frequently import batches of customer data into the > CRM > database I built for them in A2007. I created an Excel template which > they > use to supply the data, and I have an automated process that reads in > the > batch and loads the database. This is the command I use to do the > import: > > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, > BatchTable, > Import_File, True, CellRange > > All of the fields in BatchTable have a data type of 'text'. The > problem I'm > running into is that TransferSpreadsheet barfs if any of the zipcodes > in the > spreadsheet are formatted as numbers. Before A2007 I used to use an > Import > Spec, which would circumvent this problem. For some unfathomable > reason, > this feature no longer exists (that I know of). If this was a one-time > import I could deal with it by manually importing into a new table and > then > copying the data into BatchTable, but this is a recurring event. > > Any advice on how to deal with this issue? > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.