[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Delete excel row using access 2007 vba

Posted on 2011-10-24
3
Medium Priority
?
178 Views
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?

Thanks,

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
Err.Clear
Set xlbook = CreateObject("Excel.application")
'xlbook.Visible = True
Else
detectExcel
xlbook.DisplayAlerts = True

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


End If
End sub

Open in new window

0
Comment
Question by:smithrya
  • 2
3 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37019768
Try

Range.entirerow.delete
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 37020322
I would just use ActiveSheet.Rows(1).Delete
Or something like that.
0
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 2000 total points
ID: 37020327
Sorry. In your code, I would do it exactly like this
xlsheet.Rows(1).Delete
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

829 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