Delete excel row using access 2007 vba

Posted on 2011-10-24
Last Modified: 2012-05-12
Hello Experts:
            I am trying to delete the first row of a excel sheet using vba so i can convert to tab delimited and upload into a source system.

The conversion of the file works but I can't seem to delete the first row....

Any suggestions?


im fname  As String
Dim fname1 As String
Dim fname2 As String
Dim fname3 As String
Dim fname4 As String
Dim fname5 As String
Dim ExcelNot As Boolean

fname = "C:\Temp\XXXX"

DoCmd.TransferSpreadsheet acExport, , "Q_BPS_P3Hrs_Main", "C:\Temp\P3Hrs.xlsx"
DoCmd.TransferSpreadsheet acExport, , "Q_BPS_P3_Dollars_Main", "C:\Temp\P3Dollars.xlsx"
DoCmd.TransferSpreadsheet acExport, , "Q_604_Main", "C:\Temp\604.xlsx"
DoCmd.TransferSpreadsheet acExport, , "Q_BPS_1204_Main", "C:\Temp\1204.xlsx"
DoCmd.TransferSpreadsheet acExport, , "Q_BPS_205_Main", "C:\Temp\205.xlsx"

Set db = CurrentDb
On Error Resume Next
Set xlbook = GetObject(, "Excel.application")

If Err.Number <> 0 Then
ExcelNot = True     'Excel not running
Set xlbook = CreateObject("Excel.application")
'xlbook.Visible = True
xlbook.DisplayAlerts = True

Set xlsheet = xlbook.Workbooks.Open("C:\Temp\P3Hrs.xlsx").Sheets(1)
xlsheet.Application.ActiveWorkbook.SaveAs fname, -4158

End If
End sub

Open in new window

Question by:smithrya
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed

    LVL 37

    Expert Comment

    I would just use ActiveSheet.Rows(1).Delete
    Or something like that.
    LVL 37

    Accepted Solution

    Sorry. In your code, I would do it exactly like this

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now