Manipulating Excel through Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Help please. I would like to open an excel file, delete the first two rows
and name the file as a txt. Then import it into access. Below is my code to
open the file but I need help to delete the first two rows and rename it as
*.txt. Then import it into access. Help please.

Melody

Sub OpenSpecific_xlFile()
' Late Binding (Needs no reference set)
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String
Const xlText = 21


' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")


' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0


' Full path of excel file to open
On Error GoTo ErrHandle
sFullPath = CurrentProject.Path & "\Invoices.xls"


' Open it
With oXL
.Visible = True
.Workbooks.Open (sFullPath)

objWorkbook.SaveAs "C:\temp\TrialBank\NewTest.txt"

End With


ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub
 
With oXL
.Visible = True
.Workbooks.Open (sFullPath)
.Workbooks(1).Worksheets(1).Rows("1:2").Delete
.Workbooks(1).SaveAs "C:\temp\TrialBank\NewTest.txt", xlText
.Quit
End With
 
Hi Doug,

Thank you for the help I really appreciated it.
I have one more question could you tell me how to get to the one specific
worksheet that I need. I tries putting the name in the field but nothing
happened.

Thank you
Melody
 
You tried putting the name in what field?

You should be able to use:

.Workbooks(1).Worksheets("Sheet2").Rows("1:2").Delete

(or whatever the sheet's named)
 
Hi Doug,

I have it
..Sheets("AR by Cust").Select
..ActiveSheet.Range("1:2").Delete

Thank you for all your help and your website.

Melody
 
Back
Top