manipulate excel file

[datacontrol]

I need to procure code that takes an existing excel workbook and deletes row one and adds a "dummy" row of data at row 3.

I want to be able to do this via my switchboard, without the user having to open excel at all.

Is it possible or did I fall and bump my head?

This is what I have so far...not very intuitive though

Sub Macro1()
Rows("1:1").Select
Selection.delete Shift:=xlUp
End Sub

 

[IMO]

Place the following code in a new module changing "Sheet1" to the Sheet Name and "YourExcelWorkbook.xls" to the Name and path of the workbook...

Public Function DeleteRowAddDummyData()

    Dim objXL As Excel.Application
    Dim objWkb As Excel.Workbook
    Dim objSht As Excel.Worksheet
    Dim FirstRow As Long
    Dim ThirdRow As Long
    Const conSHT_NAME = "Sheet1"
    Const conWKB_NAME = "C:\YourExcelWorkbook.xls"
  
    Set objXL = New Excel.Application

        With objXL
            .Visible = False
            Set objWkb = .Workbooks.Open(conWKB_NAME)
            On Error Resume Next
            Set objSht = objWkb.Worksheets(conSHT_NAME)

                With objSht
                    FirstRow = Range("1:1").Delete
                    ThirdRow = Range("A3").Select
                    Range("A3").FormulaR1C1 = "This is Dummy Data"
                End With
                    
            objXL.ActiveWorkbook.Save
            objXL.ActiveWorkbook.Close
            objXL.Quit

        End With
  
    Set objSht = Nothing
    Set objWkb = Nothing
    Set objXL = Nothing

End Function

create a button on a form and in the OnClick event put...

Call DeleteRowAddDummyData

make sure you set a reference to
Microsoft Excel 9.0 Object Library
HTH
IMO

 

[datacontrol]

Thanks

This works great! I am tweaking now.....

 

[Randomblink]

Query: How Do I

Hey, while we're on the topic...

I am wanting to port some data out of a database INTO an Excel Spreadsheet... Using the code in your dummyfunction, how would I:

I want to label some columns...
Column 1: FundNumber
Column 2: ProjectNumber
Column 14: ProjectDescription

...If I can figure out these three, I can do the rest on my own of course...

Ok, so then I want to dump data from various tables INTO those three...

I am familiar with opening and parsing table data...

Set rst = New ADODB.Recordset

sSQLSearch = "SELECT * FROM " & TableToConvert & ";"

rst.Open sSQLSearch, CurrentProject.Connection, adOpenStatic, adLockOptimistic

Then I just rst!FieldName as I .MoveNext and all that...
However, I can't seem to get the Range to work using Labels on my Columns and Rows...

For isntance...
I tried creating a Row named: FirstEntry
Then I labeled Column 1 : FundNumber
Column 2: ProjectNumber
Column 14: ProjectDescription

But I can't figure out how to reference them with the:

With objSht
Range("FirstEntry","FundNumber").Value = rst!proj_FundNumber
End With

OR I am just confused and WILL the above work?

 

[Randomblink]

Sample of my Dilemma

Private Sub btn_ExcelMessAround_Click()
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim FirstRow As Long
Dim ThirdRow As Long
Dim sSQLSearch As String

Const conSHT_NAME = "Special Projects"
Const conWKB_NAME = "C:\My Documents\Files and Documents\Excel Spreadsheets\Special Projects Status Chart.xls"

Set objXL = New Excel.Application

With objXL
    .Visible = False
    Set objWkb = .Workbooks.Open(conWKB_NAME)
    On Error Resume Next
    Set objSht = objWkb.Worksheets(conSHT_NAME)

    Dim rst As New ADODB.Recordset
    Set rst = New ADODB.Recordset
    sSQLSearch = "SELECT * FROM tbl_ProjectBasics;"
    rst.Open sSQLSearch, CurrentProject.Connection, adOpenStatic, adLockOptimistic
                
    rst.MoveFirst

    With objSht
        Range("FirstEntry").Activate
            .Cells("FirstEntry", "FundNumber") = rst!Proj_Bdgt
            .Cells("FirstEntry", "ProjectNumber") = rst!Proj_Num
            .Cells("FirstEntry", "ProjectDescription") = rst!Proj_Descr
    End With
    rst.Close
    Set rst = Nothing

    objXL.ActiveWorkbook.Save
    objXL.ActiveWorkbook.Close
    objXL.Quit
End With

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

End Sub

Here is the code I am using with errors dancing all over... ugh!
Any help would be greatly appreciated... Good Lord I hate Excel...

 

[datacontrol]

taking this a step further

This code works great. I need to take it one step further, though. When the user clicks this button, I would like them prompted (browse) for the file to convert.

I have made use of the module at this link to open and export files:

http://www.mvps.org/access/api/api0001.htm

can I make use of this in conjunction with the code you provided in some way?

What I need to do is have the user select the file they want to convert, then hav the path and file name stored as a string, then say ConvertCSVtoXL = NewString.

Am I correct is this assumption?

Thanks in advance. I am learning! IMO- may I have your email address?