Solved Insert data from a form into a new row of an existing excel spreadsheet

[Sam Summers]

Hi everyone,
This is my second day on this and basically i don't have a clue what i'm doing!
I have tried everything i can find an am nearly there.
Basically i am trying to insert certain data from a form into an existing Excel spreadsheet - i need to insert this data into the row below the header row. Thats all and then move the row down ready for the next new record next time the user saves one from the database form.

This is the code i am currently using:

Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRow As Range
    
Set oBook = GetObject("C:\Users\Sam Summers\Desktop\Tanera Docs\Database Backups\Logistics System\June order form 21.xlsx")
Set oSheet = oBook.Sheets(1)

'insert new row
oRow(2).Insert xlShiftDown

'set value of a cell
oSheet.Cells(1, 1).Formula = Me.PONumber

'...

oBook.Save
'Set oSheet = Nothing
oBook.Close False

But i am now getting this error message - "Object variable or With Block variable not set"

Many thanks in advance

 

[Minty]

You need to keep the excel reference in place - I'm guessing your error is at this line
oRow(2).Insert xlShiftDown

It should be something like
oBook.oSheet.oRow(2).Insert xlShiftDown

You can use a with to save the typing

With oBook
   With .oSheet
         .oRow...
      .oRow etc
      etc
    End With
End With

 

[Sam Summers]

You need to keep the excel reference in place - I'm guessing your error is at this line
oRow(2).Insert xlShiftDown

It should be something like
oBook.oSheet.oRow(2).Insert xlShiftDown

You can use a with to save the typing

With oBook
   With .oSheet
         .oRow...
      .oRow etc
      etc
    End With
End With

Thanks Minty,

Tried this:

Set oBook = GetObject("C:\Users\Sam Summers\Desktop\Tanera Docs\Database Backups\Logistics System\June order form 21.xlsx")
Set oSheet = oBook.Sheets(1)

'insert new row
oBook.oSheet.oRow(2).Insert xlShiftDown

'set value of a cell
oSheet.Cells(1, 1).Formula = Me.PONumber

'...

oBook.Save
'Set oSheet = Nothing
oBook.Close False

But now getting the error - "Object doesn't support this property or method"

I'm completely baffled after two days at it!

 

[arnelgp]

use excel.application:

Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRow As Range
    
Set oExcel = CreateObject("excel.application")
Set oBook = oExcel.Workbooks.Open(Environ("userprofile") & "\downloads\created.xlsx")
Set oSheet = oBook.Sheets(1)

'insert new row
oRow(2).Insert xlShiftDown

'set value of a cell
oSheet.Cells(1, 1).Formula = Me.PONumber

'...

oBook.Save
'Set oSheet = Nothing
oBook.Close True
Set oBook = Nothing
oExcel.Quit
Set oExcel = Nothing

 

[Sam Summers]

Hi guys and thank you for trying to help someone as stupid as me.....

I now have these various bits of code and nothing is working because i just don't know what i am doing

Set oExcel = CreateObject("excel.application")
'Set oBook = oExcel.Workbooks.Open(Environ("userprofile") & "\downloads\created.xlsx")
Set oBook = oExcel.Workbooks.Open(Environ("C:\Users\Sam Summers\Desktop\Tanera Docs\Database Backups\Logistics System\June order form 21.xlsx"))
'Set oBook = GetObject("C:\Users\Sam Summers\Desktop\Tanera Docs\Database Backups\Logistics System\June order form 21.xlsx")
Set oSheet = oBook.Sheets(1)

 

[arnelgp]

Set oExcel = CreateObject("excel.application")
Set oBook = oExcel.Workbooks.Open("C:\Users\Sam Summers\Desktop\Tanera Docs\Database Backups\Logistics System\June order form 21.xlsx")
Set oSheet = oBook.Sheets(1)

 

[Sam Summers]

Set oExcel = CreateObject("excel.application")
Set oBook = oExcel.Workbooks.Open("C:\Users\Sam Summers\Desktop\Tanera Docs\Database Backups\Logistics System\June order form 21.xlsx")
Set oSheet = oBook.Sheets(1)

Thanks Arnel, i'm a bit closer.
I've never referenced Excel before and i just cannot seem to get the data "PONumber" into the cell A2

Once i crack this i will then insert other data from the form into other cells along the new row that is created on row 2 every time the code is run.

I have tried so many different permutations and trying different things from searches but i either get error messages or nothing?

I'm currently trying this which doesn't work:

'insert new row
Range(2, 2).EntireRow.Insert

'set value of a cell
oSheet.Cells(1, 1).Formula = Me.PONumber

Can anyone point me in the right direction?

 

[Gasman]

arnelgp might asleep now?

You cannot use Range just as you would in Excel.
You need to qualify everything as you are using it from Access, so try oSheets.Range

I would have thought you would want to insert in the next available row each time?, so you need to find the last used row, and increment the row number?

I use this in Excel, you will need to modify for Access as I mentioned above. And the column has to be one that always has data in the row? sht is a worksheet object.

iLastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

HTH

 

[Sam Summers]

So day 3 and i am no closer to solving this now.
I just do not have a clue how to take the data from a few fields from my form and insert them in preferably a new row inserted in row 2 of an existing spreadsheet "C:\Users\Sam Summers\Desktop\Tanera Docs\Database Backups\Logistics System\June order form 21.xlsx"

 

[Saphirah]

If you just need headers/descriptions on the first row and then insert all your data afterwards in your spreadsheet you can build a query with the correct data and then use the following code to export the data to an excel sheet.
This might not be an optimal solution because it will override the file every time, instead of just appending the new rows but it is a solution that is working for me.

Sub ExportQueryAsExcel()
    Dim sFileName As String
    sFileName = "Path/To/File.xlsx"
    DoCmd.OutputTo acOutputQuery, "QueryNameHere", acFormatXLSX, sFileName, True
End Sub

And as long as you keep your data in access this should not be a problem.

 

[Minty]

Can you post up a stripped-down version of your database, just enough so we have some of the data you need and an example of the existing spreadsheet?

I could create a small db to do this from scratch but it would be easier with an existing starting point.
Your problem is you are not including the entire qualification to the objects you are manipulating.

Below is code that opens an existing file and formats the contents into a table, maybe it will point you in the right direction;

Public Sub XLFormatTable(sFile As String, sSheet As String, Optional bOpen As Boolean = True)

    On Error GoTo XLFormatTable_Error
    ' Late binding to avoid reference:
    Dim xlApp            As Object        'Excel.Application
    Dim xlWb             As Object        'Workbook
    Dim xlWS             As Object        'Worksheet
    Dim tbl              As Object
    Dim rng              As Object
     
    'Debug.Print sFile, sSheet
     
    ' Create the instance of Excel that we will use to open the temp book
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = bOpen
    Set xlWb = xlApp.Workbooks.Open(sFile)
    'Debug.Print xlWB.Name
    Set xlWS = xlWb.Worksheets(sSheet)

    ' Format our temp sheet
    ' ************************************************** *************************

    xlApp.range("A1").Select

    With xlWS
        '        With .UsedRange
        '            .borders.LineStyle = xlContinuous
        '            .borders.ColorIndex = 0
        '            .borders.TintAndShade = 0
        '            .borders.Weight = xlThin
        '        End With
        '
        '        'format header 90 degree
        '        With .Range("i1:y1")
        '            .HorizontalAlignment = xlCenter
        '            .VerticalAlignment = xlBottom
        '            .WrapText = False
        '            .Orientation = 90
        '            .AddIndent = False
        '            .IndentLevel = 0
        '            .ShrinkToFit = False
        '            .ReadingOrder = xlContext
        '            .MergeCells = False
        '        End With
        '        .UsedRange.Rows.RowHeight = 15
        '        .UsedRange.Columns.AutoFit

        With xlWb.Sheets(sSheet)
            Set rng = .Cells(1, 1).CurrentRegion
        End With
        Set tbl = xlWS.ListObjects.Add(xlSrcRange, rng, , xlYes)
        tbl.TableStyle = "TableStyleMedium2"
        tbl.ShowTotals = False
        xlWS.Cells.EntireColumn.AutoFit
     
    End With
       
    xlWb.Save
   
    If Not bOpen Then
        xlApp.Workbooks.Close
        Set xlApp = Nothing
    Else
        xlApp.ActiveWindow.WindowState = xlMaximized
    End If
   
   
    On Error GoTo 0
    Exit Sub

XLFormatTable_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure XLFormatTable, line " & Erl & "."

End Sub

 

[Cronk]

The problem initially was that the oRow object variable had not been set.
Try

Set oSheet = oBook.Sheets(1)
oSheet.Rows(2).Insert xlShiftDown

That will create a line under your header row (Row 1)

The next problem is that Cell(1,1) is on the header row. I think you want

oSheet.Cells(3,1)= ...

 

[Sam Summers]

So here is my DB and the Spreadsheet the problem is from the Create PO form i want to add the data from the fields "PONumber" into the cell PO, "PODate" into the cell "Date order sent", "OrderedBy" into the cell "Person ordering", "OtherField" into the cell "Item & Description", "Quantity" into the cell "Units to order", "OrderValue" into "Total cost", "ETA" into "ETA & shipping method" and "SiteID" into "Destination".

That's it really......

 

[Sam Summers]

And now its asking me if i want to replace the spreadsheet?

 

[Gasman]

I cannot open the DB due to my old access version, so that is me out. Sorry.

 

[Sam Summers]

I cannot open the DB due to my old access version, so that is me out. Sorry.

Thank you for your help though

 

[arnelgp]

check and test.

 

[Sam Summers]

check and test.

Once again Thank you but for some reason i keep getting the message asking me if i want to replace the file which i don't?

 

[arnelgp]

I did not get that msg.

 

[Sam Summers]

I did not get that msg.

Ok, what was happening was Excel (or Access) was still holding something in memory and after i restarted my laptop your code all worked Arnel so many many thanks once again!!!