Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201

    Unanswered: ADP file Problem

    Hello

    I have a problem and need some assistance.
    I have a SS that I am loading into a SQL2000 database
    I am using DTS for this.
    Previously uses used an ADP file to load the spreadsheet.

    My problem is that the ADP file uses the ACTIVESHEET for its import.
    And the in the DTS you must select the SS you wish to insert.
    And the SS name is not standard.

    I am trying to use the ADP file and rename the worksheet
    any idea how to do this or any idea howelse I can accomplish my goal.

    TIA

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The properties you describe are for both access and excel objects. Could you clarify what you're trying to do here? Remember, we know absolutely nothing about your project.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    I have a excel workbook that could have one or may sheets in it.
    The sheet names are not standard.
    They are saved by a export to excel process from a 3rd party vendor.
    The SS name inherits the name on the query used to create it.
    The users save that workbook on the sheet that they wish to import.

    Access will when you transfer a SS will take the sheet that is active if none is specified

    SQL server needs to have the sheet specified by name

    I can go into the SS and change the name of the activesheet manually to a standard name that then I can specify into DTS in SQL server.

    I wish to do the same thing via a program
    similiar to this (recorded via EXCEL when i did this manually)

    Workbooks.Open strPath & DestinationFile
    'MsgBox activesheet.Name
    activesheet.Name = "LOADTHIS"
    'Range("A1").Select
    ActiveWorkbook.Save
    ActiveWorkbook.Close

    sorry for being to vauge before. Hope this helps

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's pretty close to what you need to do in Access. Make sure you have areference to the Microsoft Excel 10.0 Object Library. After that, you can manipulate excel spreadsheets with the exact same code.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201

    Talking Figured it out Thanks code for others

    After some research and help from a much smarter associate than myself
    this is the code.

    Public Function SendData(ByVal SourceFile As String, ByVal DestinationFile As String)

    Dim lngColumn As Long
    Dim xlx As Object, xls As Object, xlc As Object, xlw As Object


    '--- copy and paste data into new workbook then save -----------------------------


    On Error Resume Next
    strPathSql = "Pathname"
    strPath = "Pathname1"

    If Dir(strPath & DestinationFile) <> "" Then Kill (strPath & DestinationFile)

    FileCopy SourceFile, strPath & DestinationFile

    Set xlx = CreateObject("Excel.Application")
    Set xlw = xlx.Workbooks.Open(strPath & DestinationFile) ',,True
    xlw.activesheet.Name = "LOADTHIS"
    xlx.ActiveWorkbook.Save
    xlx.ActiveWorkbook.Close

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •