Results 1 to 4 of 4
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Open Spreadsheet in Access to read data (2003/SP2)

    We have a form in Access that has a button to browse for files that will be manipulated in Access. The file could be an Excel spreadsheet or a Text file. I want to open the file; read the headings if it is Excel or parse the headings if it is Text.

    I have the piece of code that is all over the web from Ken Getz to open the File Dialog. I cannot figure out how to actually Open the file and use it.

    If it was Excel <img src=/S/smile.gif border=0 alt=smile width=15 height=15> it would be so easy to record the method and tear it down for analysis.
    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Spreadsheet in Access to read data (2003/SP2)

    You get a file name from the open dialog. you can use this file name in further code.

    If you want to manipulate an Excel workbook, you'll have to use Automation to control Excel from Access. See WendellB's tutorial Automation 101 - it provides an introduction and useful links.

    If you want to manipulate a text file, you can use the 'old' Basic instructions such as Open, Line Input and Close to do so.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Spreadsheet in Access to read data (2003/SP2)

    I do this frequently and have a ton of code. If you need it, let me know.

  4. #4
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Open Spreadsheet in Access to read data (2003/SP2)

    Kathryn -- I probably need your ton of code.

    Here is where I am now. Went to Tools|References and checked Excel Object Library so that I could run Excel. This piece of code opens the spreadsheet but does not do anything after that.
    <pre>Sub GetExcelSpread()
    Dim xlApp As New Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim Filt As String, fIndex As Integer
    Dim Title As String, FileNamer As Variant
    Dim LastColm As Long
    Dim HeadingsArr As Variant
    Dim ExcelRunning As Boolean
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True <font color=red>'Temporary on while testing 'Work in the background</font color=red>
    Filt = "Text files (*.txt),*.txt," & _
    "Excel files (*.xls),*.xls," & _
    "Comma Separated (*.csv),*.csv," & _
    "All files (*.*),*.*"
    fIndex = 2 <font color=red> 'default to Excel</font color=red>
    Title = "Select a file to Import"
    FileNamer = xlApp.GetOpenFileName(FileFilter:=Filt, _
    FilterIndex:=fIndex, Title:=Title)
    Set xlBook = xlApp.Workbooks.Open(FileNamer) <font color=red> 'open the file
    'The file opens and then none of the following code is executed
    ''Is Access shifting control to Excel?</font color=red>
    With xlBook
    xlSheet = ActiveSheet.Name
    xlSheet.Range("A2").select
    LastColm = xlSheet.Range("IV1").End(xlleft).Column <font color=red>'Last Column</font color=red>
    Debug.Print "LastColumn= " & LastColm
    End With
    ReDim HeadingsArr(0 To LastColm) <font color=red> 'Plan to fill array with headings</font color=red>

    End Sub</pre>

    How do I keep execution going? Can I pause for someone to choose a sheet from the workbook? Am I expecting too much?
    Alan

Posting Permissions

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