Results 1 to 11 of 11
  1. #1
    Join Date
    May 2006
    Location
    Australia
    Posts
    11

    Unanswered: Put value into Excel Cell

    Hi all, newbie here!

    I have never done this put I know it can be done!!!

    I have an access database and a spreedsheet call test.xls.

    All I want to do is put an array data into an excel sheet using Micorosoft Excel 11 Object Library.

    Heres the code I have got so far:

    Dim strScoreCardPath As String
    Dim strReportPath As String
    Dim strFileName As String
    Dim xlApp As Excel.Application
    Dim xlBook As Workbook
    Dim xlSheet As Worksheet

    strScoreCardPath = "C:\Documents and Settings\user\Desktop\Access DB\ScoreCards\"
    strReportPath = "C:\Documents and Settings\user\Desktop\Access DB\Reports\"
    strFileName = "test.xls"

    ' This sets the variables related to the Excel file
    Set xlBook = GetObject(strReportPath & strFileName)
    Set xlApp = xlBook.Parent
    Set xlSheet = xlBook.Worksheets("Sheet1")

    xlSheet.Cells(A1).Select
    xlSheet.Cells(A1) = "me"


    When the code runs I get the following error:

    Run-time error '1004'
    Application-defined or object-defined error

    All I want to do is put data into cell A1

    The next thing I want to do is put data into A1 to D1 using an array.

    Thanking you all in advance.

    - Mark

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Mark

    there are loads of properties that affect the contents of a cell. I've never really been 100% sure what the difference is. I just stick with
    Code:
    xlSheet.Cells(A1).Formula = "xyz"
    which works just fine.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You do not say on which line the error occurs in the code, but one thing I notice is that the xlSheet.cells(A1) should be xlSheet.Range("A1") OR xlSheet.Cells(1,1) !?

    But I think the error is before this line as this produced a compile error (if you are early binding).


    Do you have a referance to the Excel library, or are you late bunding ?

    MTB

  4. #4
    Join Date
    May 2006
    Location
    Australia
    Posts
    11
    Hi I do a have a reference to the excel object libary and the error occurs on this line:

    xlSheet.Cells(A1) = "me"

    Kind Regards,

    Mark

  5. #5
    Join Date
    May 2006
    Location
    Australia
    Posts
    11
    Ive tried: xlSheet.Cells(A2).Formula = "2003" and get: variable not defined highlighting A2

    I tried xlSheet.Cells(A2).Formula = "2003" and got: type mismatch

    I tried xlSheet.Range("A2") = "2003" and got no error put it didnt put any data into the spreedsheet!!!!

  6. #6
    Join Date
    May 2006
    Location
    Australia
    Posts
    11
    Furthmore after I try the last line I cannot open the excel document. Excel just wont load it and there is no error.

    Heres the new code:

    Private Sub Command1_Click()
    Dim strFileName As String
    Dim xlApp As Excel.Application
    Dim xlBook As Workbook
    Dim xlSheet As Worksheet

    strFileName = "C:\Documents and Settings\Mark\Desktop\test.xls"

    Set xlBook = GetObject(strFileName)
    Set xlApp = xlBook.Parent
    Set xlSheet = xlBook.Worksheets("Sheet1")

    xlSheet.Range("A2") = "2003"
    xlBook.Save
    Set xlBook = Nothing
    Set xlApp = Nothing
    Set xlSheet = Nothing
    End Sub

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    The only way I could make it work, and have the workbook unhidden, is to creat an Excel Application object first, as follows:-

    Code:
    Private Sub Command1_Click()
    Dim strFileName As String
    Dim xlApp As Excel.Application
    Dim xlBook As Workbook
    Dim xlSheet As Worksheet
    
    'strFileName = "C:\Documents and Settings\Mark\Desktop\test.xls"
    strFileName = "C:\TEMP\test.xls"
    
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.Open strFileName
    Set xlBook = xlApp.ActiveWorkbook
    
    Set xlSheet = xlBook.Worksheets("Sheet1")
    
    xlSheet.Range("A2") = "2003"
    
    xlBook.Close True    ' True = Saves Changes 
    
    xlApp.Quit
    
    Set xlBook = Nothing
    Set xlApp = Nothing
    Set xlSheet = Nothing
    
    End Sub
    Why creating a workbook object leaves it hidden I have not worked out yet. I could not make it unhidded it code either, hence the Excel Object (which is may normal method).

    Does that help ?


    MTB

  8. #8
    Join Date
    May 2006
    Location
    Australia
    Posts
    11

    Talking I found this code did the job!

    Hi all, thank you all very much for your help. I found the following snippet of code worked for me!

    It does run quite fast. Takes about 20 minutes to do 749 excel reports!!!

    Ok, you might be thinking, geez, to run that code? Well I do have about 300 fields of data pulled from 3 differnt databases to create one report.

    Thought I would show the code what worked for me incase someone else stumbles on this forum and needs to know how to get data into an excel sheet.

    Once again, thank you everyone for helping me solve this issue.

    Kind Regards,

    Mark

    and the code ....

    Set oExcelApp = CreateObject("EXCEL.APPLICATION")
    oExcelApp.Visible = False
    oExcelApp.Workbooks.Open strFileName
    Set oWs = oExcelApp.ActiveSheet
    Set oWb = oExcelApp.ActiveWorkbook

    With oWs
    .Cells(3, 1) = "test"
    End With

    oWb.Save

    oExcelApp.Quit


    this code uses ms excel object library 11

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by byte_logic
    It does run quite fast. Takes about 20 minutes to do 749 excel reports!!!

    Ok, you might be thinking, geez, to run that code? Well I do have about 300 fields of data pulled from 3 differnt databases to create one report.
    Geez, to run that code?

    Are you writing a cell at a time from a recordset? There is an excel method that copies the contents of a recordset into excel in one go:
    Code:
    Option Compare Database
    Option Explicit
     
    Sub ExportData()
    On Error GoTo ExportData_Error
     
    'DAO objects to get the data
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
     
    'Excel objects to manipulate Excel
        Dim exApp As Excel.Application
        Dim exBook As Excel.Workbook
        Dim exSheet As Excel.Worksheet
     
    'variables to use for formatting loops
        Dim NoOfCols As Integer
        Dim NoOfRows As Integer
     
    'Iterant for misc loops
        Dim i As Integer
     
        Set db = Application.CurrentDb
     
    'Get the data
        Set rs = db.OpenRecordset("SELECT * FROM MyTable")
     
    'Instantiate the excel objects
        Set exApp = New Excel.Application
     
        Set exBook = exApp.Workbooks.Add
     
        exApp.Visible = True
     
        exApp.Interactive = False
     
        Set exSheet = exBook.Worksheets(1)
        If Not rs.EOF Then rs.MoveLast: rs.MoveFirst
     
    'Populate the variables
        NoOfCols = rs.Fields.Count
        NoOfRows = rs.RecordCount
     
    'Pop the data into Excel
        exSheet.Range("A2").CopyFromRecordset rs
     
    'Write in the column headings
        For i = 0 To NoOfCols - 1
     
            exSheet.Cells(1, i + 1).Value = rs.Fields(i).name
     
        Next i
     
    'Use our variables to format the data populated cells ONLY
        exSheet.Cells.Range("A1", ExcelCodes(NoOfCols) & 1).Interior.Color = vbYellow
     
    'And again - using both this time
        exSheet.Cells.Range("A1", ExcelCodes(NoOfCols) & (NoOfRows + 1)).Borders.Color = RGB(0, 0, 0)
    'Adjust column widths
        exSheet.Columns.EntireColumn.AutoFit
     
    'Save it
        exBook.SaveAs "C:\Temp\Temp.xls"
     
    ExportData_Exit:
    'Very important - always account for in error trap
        exApp.Interactive = True
     
    'Clean Up
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
        Set exSheet = Nothing
        Set exBook = Nothing
        Set exApp = Nothing
     
        Exit Sub
     
    ExportData_Error:
     
        MsgBox Err.Description
        Resume ExportData_Exit
    End Sub
     
    Function ExcelCodes(ByVal intColNo As Integer) As String
     
        Dim strCol As String
     
        Do While intColNo > -1
            If intColNo > 26 Then
                strCol = Chr(64 + ((intColNo - 1) \ 26))
                intColNo = intColNo - (26 * ((intColNo - 1) \ 26))
            Else
                strCol = strCol & Chr(64 + intColNo)
                Exit Do
            End If
        Loop
     
        ExcelCodes = strCol
     
    End Function
    This is some code I wrote for the forum a bit ago. The key line is in red. It may be applicable - it may not. I notice I used Cells.Value here lol.

    If you can't be bothered reading it to figure out what it does - create a new db. Create a reference to Excel.

    Paste this in to the SQL View of a query and run:
    Code:
    CREATE TABLE MyTable (MyCol Text(25), MyOtherCol Text(25))
    Paste this in the SQL view and run several times:
    Code:
    INSERT INTO MyTable (MyCol, MyOtherCol) VALUES ("Foo", "Bar")
    Paste the vb in a module and run.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    May 2006
    Location
    Australia
    Posts
    11
    Hi, the problem I have is that it needs to paste the data into several worksheets and in differnt cells.

    for example:

    sheet1: cells: a1, b7, c2, d7, f1
    sheet2: cells: a1 to b12

    etc etc

  11. #11
    Join Date
    May 2006
    Location
    Australia
    Posts
    11
    it did not do the last record and did not copy the data across correctly

    Quote Originally Posted by pootle flump
    Geez, to run that code?

    Are you writing a cell at a time from a recordset? There is an excel method that copies the contents of a recordset into excel in one go:
    Code:
    Option Compare Database
    Option Explicit
     
    Sub ExportData()
    On Error GoTo ExportData_Error
     
    'DAO objects to get the data
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
     
    'Excel objects to manipulate Excel
        Dim exApp As Excel.Application
        Dim exBook As Excel.Workbook
        Dim exSheet As Excel.Worksheet
     
    'variables to use for formatting loops
        Dim NoOfCols As Integer
        Dim NoOfRows As Integer
     
    'Iterant for misc loops
        Dim i As Integer
     
        Set db = Application.CurrentDb
     
    'Get the data
        Set rs = db.OpenRecordset("SELECT * FROM MyTable")
     
    'Instantiate the excel objects
        Set exApp = New Excel.Application
     
        Set exBook = exApp.Workbooks.Add
     
        exApp.Visible = True
     
        exApp.Interactive = False
     
        Set exSheet = exBook.Worksheets(1)
        If Not rs.EOF Then rs.MoveLast: rs.MoveFirst
     
    'Populate the variables
        NoOfCols = rs.Fields.Count
        NoOfRows = rs.RecordCount
     
    'Pop the data into Excel
        exSheet.Range("A2").CopyFromRecordset rs
     
    'Write in the column headings
        For i = 0 To NoOfCols - 1
     
            exSheet.Cells(1, i + 1).Value = rs.Fields(i).name
     
        Next i
     
    'Use our variables to format the data populated cells ONLY
        exSheet.Cells.Range("A1", ExcelCodes(NoOfCols) & 1).Interior.Color = vbYellow
     
    'And again - using both this time
        exSheet.Cells.Range("A1", ExcelCodes(NoOfCols) & (NoOfRows + 1)).Borders.Color = RGB(0, 0, 0)
    'Adjust column widths
        exSheet.Columns.EntireColumn.AutoFit
     
    'Save it
        exBook.SaveAs "C:\Temp\Temp.xls"
     
    ExportData_Exit:
    'Very important - always account for in error trap
        exApp.Interactive = True
     
    'Clean Up
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
        Set exSheet = Nothing
        Set exBook = Nothing
        Set exApp = Nothing
     
        Exit Sub
     
    ExportData_Error:
     
        MsgBox Err.Description
        Resume ExportData_Exit
    End Sub
     
    Function ExcelCodes(ByVal intColNo As Integer) As String
     
        Dim strCol As String
     
        Do While intColNo > -1
            If intColNo > 26 Then
                strCol = Chr(64 + ((intColNo - 1) \ 26))
                intColNo = intColNo - (26 * ((intColNo - 1) \ 26))
            Else
                strCol = strCol & Chr(64 + intColNo)
                Exit Do
            End If
        Loop
     
        ExcelCodes = strCol
     
    End Function
    This is some code I wrote for the forum a bit ago. The key line is in red. It may be applicable - it may not. I notice I used Cells.Value here lol.

    If you can't be bothered reading it to figure out what it does - create a new db. Create a reference to Excel.

    Paste this in to the SQL View of a query and run:
    Code:
    CREATE TABLE MyTable (MyCol Text(25), MyOtherCol Text(25))
    Paste this in the SQL view and run several times:
    Code:
    INSERT INTO MyTable (MyCol, MyOtherCol) VALUES ("Foo", "Bar")
    Paste the vb in a module and run.

    HTH

Posting Permissions

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