'Steve Goodhall'
steve at goodhall.info
Thu Jun 23 09:41:38 CDT 2011
Another option would be to open the Excel workbook as an ODBC data source. You can find code for this on my web site, www.goodhall.info [1] under "Useful Software". I have not used this in a long time so there may be issues when you try it in Access 2007 or 2010. Regards, Steve Goodhall, MSCS, PMP 248-505-5204 ----- Original Message ----- From:Access Developers discussion and problem solving To:"Access Developers discussion and problem solving" Cc: Sent:Thu, 23 Jun 2011 07:53:36 -0500 Subject:Re: [AccessD] Automating Excel imports - How to do the basics 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 [2] [mailto:accessd-bounces at databaseadvisors.com [3]] 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 [4],ZipColNum).value = "'" & oSht.Cells(I [5], 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 [6] [mailto:accessd- > bounces at databaseadvisorscom [7]] 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 [8] 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 [9] > http://databaseadvisors.com/mailman/listinfo/accessd [10] > Website: http://www.databaseadvisors.com [11] -- AccessD mailing list AccessD at databaseadvisors.com [12] http://databaseadvisors.com/mailman/listinfo/accessd [13] Website: http://www.databaseadvisors.com [14] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- AccessD mailing list AccessD at databaseadvisors.com [15] http://databaseadvisors.com/mailman/listinfo/accessd [16] Website: http://www.databaseadvisors.com [17] Links: ------ [1] http://www.goodhall.info [2] mailto:accessd-bounces at databaseadvisors.com [3] mailto:accessd-bounces at databaseadvisors.com [4] http://sitemail.gate.com/http:/ [5] http://sitemail.gate.com/http:/ [6] mailto:accessd-bounces at databaseadvisors.com [7] mailto:bounces at databaseadvisors.com [8] http://docmd.transferspreadsheet/ [9] mailto:AccessD at databaseadvisors.com [10] http://databaseadvisors.com/mailman/listinfo/accessd [11] http://www.databaseadvisors.com/ [12] mailto:AccessD at databaseadvisors.com [13] http://databaseadvisors.com/mailman/listinfo/accessd [14] http://www.databaseadvisors.com/ [15] mailto:AccessD at databaseadvisors.com [16] http://databaseadvisors.com/mailman/listinfo/accessd [17] http://www.databaseadvisors.com/