[AccessD] [Spam]8.51 Re: Automating Excel imports

Here's  the code I use in a module to open an Excel object:
You'll need to set up a reference to the Excel object library.


Public myXLS As Excel.Application
Public gboolRunning As Integer

Public Function MSExcelOpen()

   On Error Resume Next
   gboolRunning = True

   ' See if Excel is already running
   Set myXLS = GetObject(, "Excel.Application")
   If myXLS Is Nothing Then
      ' If it was not running start a new instance
      Set myXLS = New Excel.Application
      ' Set myXLS = CreateObject("Excel.Application")
      gboolRunning = False
     Else
      'kill it and start again so screen doesn't lock up ???
      myXLS.Quit
      Set myXLS = Nothing
      Set myXLS = New Excel.Application
   End If

   If myXLS Is Nothing Then
      MsgBox "Can't Create Excel Object"
      MSExcelOpen = False
   Else
      myXLS.Visible = True
      MSExcelOpen = True
   End If

End Function


On Wed, Jun 22, 2011 at 6:36 PM, Darrell Burns <dhb at flsi.com> wrote:
> Hey Mark, could you give me a jump start on how to initiate an Excel
> instance?
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms
> Sent: Wednesday, June 22, 2011 4:01 PM
> To: 'Access Developers discussion and problem solving'
> Subject: [Spam]8.51 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>