INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Deleting access rows

Deleting access rows

(OP)
Hey, I am creating an access database to automate project hours metrics reporting. I want it as fully automated as I can so the manager just clicks buttons. . Is what we do is import excel spreadsheets into the database then run queries on them to generate reports. The issue I have is the last lines have non needed data in them. I would like to either delete those lines prior to import or when importing skip them. The import code is

CODE

DoCmd.TransferSpreadsheet acImport, 8, "TIME_SUMMARY", strpath & strfile, True 
The bottom of the spreadsheets looks like the following:

49 PROJECT 0 Select Project Nbr
50 Insert new line above this line only. Please perform insert first than copy paste existing line and modify. 0.00 10.00 10.50 9.00 7.00 8.00 0.00 44.50

I realize I could redo my code with

CODE -->

DoCmd.TransferSpreadsheet acImport, 8, "TIME_SUMMARY", strpath & strfile, True, "A7:P49" 
but that would mean every time they added or removed a project from the time sheet I would have to modify the module. There are several managers that may be interested in this database each with a different number of projects on it so it could be tough. Is what I would like to do is either delete the lines after the last line that contains the word project in column 1 or skip any lines that have the word insert in column 1. Any ideas?

Cretin

RE: Deleting access rows

Before DoCmd.TransferSpreadsheet command, I would open the Excel file, delete unwanted rows, save the file, and then run this command.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Deleting access rows

(OP)
I agree I am just having an issue figuring out how to delete unwanted rows

Cretin

RE: Deleting access rows

Do you know how to open an Excel file from Access' VBA?
If so, do you know how to loop down the rows in the file?
If so, do you know how to find the first row you want to delete? (I would guess the row with "49 PROJECT 0 Select Project Nbr " data in it.
Do you know how many rows you need to delete?
Do you know how to Save the Excel file and exit Excel - in VBA?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Deleting access rows

(OP)

Quote:

Do you know how to open an Excel file from Access' VBA?
Yes

Quote:

If so, do you know how to loop down the rows in the file?
No

Quote:

If so, do you know how to find the first row you want to delete? (I would guess the row with "49 PROJECT 0 Select Project Nbr " data in it.
No

Quote:

Do you know how many rows you need to delete?
No

Quote:

Do you know how to Save the Excel file and exit Excel - in VBA?
Yes

Is what I need to do is determine which row is the end. I can do that by either cycling through it and when column 1 changes from project I delete anything after that or delete the line that contains insert in it. I have been looking I am not sure how to do that.

Cretin

RE: Deleting access rows

Let's say in column I you have some data, and as soon as you find text in this column that says: "Project', you delete that row + 5 rows down from that row:

CODE

Dim xlApp As Object
Dim L As Long
Dim blnKeepGoing As Boolean

blnKeepGoing = True
'Assuming first row is a header row
L = 2

Set xlApp = CreateObject("Excel.Application")

With xlApp
    .Visible = True
    .Workbooks.Open FileName:="C:\TEMP\MyFile.xlsx"
    
    Do While blnKeepGoing
        If InStr(UCase(.Range("I" & L).Value), "PROJECT") > 0 Then
            .Rows(L & ":" & L + 5).Delete
            blnKeepGoing = False
        End If
        L = L + 1
    Loop
    
    .ActiveWorkbook.Save
    .Quit
End With

Set xlApp = Nothing 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Deleting access rows

(OP)
I have made great progress, thank you. However I( still have one issue

CODE -->

Set x1App = CreateObject("Excel.application")

With x1App
   .Visible = True
 .Workbooks.Open FileName:="U:\Projects\timetracker\testing\ann_Timesheet_20160806.xlsx" 
 .Workbooks.Open FileName:=strpath & strfile
  Do While binkeepgoing
     If InStr(UCase(.Range("I" & L).Value), "Project") > 0 Then 


On the bolded code I get the error run time error 1004: application defined or object defined error.

Cretin

RE: Deleting access rows

(OP)
now I am getting object required on the bolded section:

CODE -->

Do While binkeepgoing
     If InStr(UCase(.Range("I" & L).Value), "Project") > 0 Then
     .Rows(L & ":" & L + 5).Delete 

Cretin

RE: Deleting access rows

(OP)
I did figure that out but now it cycles through in an endless loop without deleting the rows.

Basically I am looking for a row that starts with insert and delete that row. The way I am doing it is opening the worksheet, cycle through the rows, based on the value L I think that works then I want to delete the row which does not work.

CODE -->

Public Function delete_rows()



Dim strfile As String
Dim strpath As String
Dim x1App As Object
Dim L As Long
Dim binkeepgoing As Boolean
Dim Workbooks
Dim FileName


Dir (strpath & "*Timesheet*.xlsx")
strpath = "U:\Projects\time tracker\testing\"
strfile = Dir(strpath & "*Timesheet*.xlsx")
ChDir (strpath)

binkeepgoing = True
L = 2

Set x1App = CreateObject("Excel.application")

With x1App
   .Visible = True
 '.Workbooks.Open FileName:="U:\Projects\timetracker\testing\ann_Timesheet_20160806.xlsx"
 .Workbooks.Open FileName:=strpath & strfile
  Do While binkeepgoing
     If InStr(UCase(.Range("A" & L).Value), "Project") > 0 Then
     .Rows(L & ":" & L + 5).Delete
    binkeepgoing = False
    End If
   
   L = L + 1
  Loop
  
  .Activeworksheet.Save
  .Quit
End With

Set x1App = Nothing

End Function 

Cretin

RE: Deleting access rows

(OP)
Based on debug it is skipping the bolded line

CODE

With x1App
   .Visible = True
 '.Workbooks.Open FileName:="U:\Projects\timetracker\testing\ESH_Timesheet_20160806.xlsx"
 .Workbooks.Open FileName:=strpath & strfile
  Do While binkeepgoing
     If InStr(UCase(.Range("A" & L).Value), "Project") > 0 Then
     .Rows(L & ":" & L + 5).Delete 
    binkeepgoing = False
    End If
   
   L = L + 1
  Loop 

Cretin

RE: Deleting access rows

"I am looking for a row that starts with [the word?] insert and delete that row"

This line of code:
If InStr(UCase(.Range("A" & L).Value), "Project") > 0 Then

looks for a word "PROJECT" (not "insert")

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Deleting access rows

(OP)
I changed it back and it is doing the same thing it skips the line that deletes

CODE

.Workbooks.Open FileName:=strpath & strfile
  Do While binkeepgoing
     If InStr(UCase(.Range("A" & L).Value), "Project") > 0 Then
     .Rows(L & ":" & L + 5).Delete
    binkeepgoing = False
    End If
   
   L = L + 1
  Loop 

Cretin

RE: Deleting access rows

OK, change this line to:

CODE

If InStr(UCase(.Range("A" & L).Value), UCase("Project")) > 0 Then 

And if you have something like this in Column A in Excel:

     A
1  AbcD
2  1234
3  Some PrOjeCt Here
4  Something
 
This code should Delete BLUE rows.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Deleting access rows

(OP)
ok the following is the row I need to delete

Insert new line above this line only. Please perform insert first than copy paste existing line and modify.

Cretin

RE: Deleting access rows

Assuming this text is in the column A, try:

CODE

If InStr(UCase(.Range("A" & L).Value), UCase("Insert new line above this line only")) > 0 Then 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Deleting access rows

(OP)
Thank you all so much I think I have it now. As I typically do when I have an issue I get a lot of help with I will send a small donation to TekTips

Cretin

RE: Deleting access rows

It would be nice if you could show your final code so others - who may have the same question - can benefit from your experience.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Deleting access rows

(OP)
you're right my apologies.

Frankly I was not able to get the delete to work exactly how I wanted it to. Is what I ended up doing is defining another field in the table, that line got written to there then delete that record from the table.

Overall is what I am doing is I am importing a series of time sheets into a table. Then I have queries that generate the reports by the week and another one that will generate reports between 2 spe4cified dates.

Cretin

RE: Deleting access rows

Cretin,

Glad you got your problem sorted out or mostly worked out, great that you hung in there till the end. Some suggestions:
  • If you consider it complete, go ahead and mark whatever thread from Andy helped you the MOST with the "Great post" - you can only use that on one thread, so that's why I say "the most" it helps when people are looking for a similar solution, and it gives a pat on the back to Andy.
  • As Andy mentioned, it'd be good to post your final code, so others can refer to it as an example and find their issues.
  • An Excel specific thing: In my experience, when I want to loop through rows or columns, I have found it easier/better to use Cells() instead of Range(). Both work, but Cells() has worked better for loops when I've used a For x = SmallerNumber to BiggerNumber loop.
  • Another Excel item: I may have overlooked it, but I didn't see a variable for the workbook or worksheet, just an xlApp variable. It can make your code easier for you to follow and refer to later, I think, if you use variables for the workbook and worksheet by using variables for those objects.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

RE: Deleting access rows

(OP)
I actually did it differently for supportability reasons we are not an access or VB shop so if I am not around my boss will have to fix it herself since it is for her which with my documentation she should.

Is what I ended up doing is define a field in the table with that data. I already have a query to delete any records with 0's in them since if a project did not get any work done on it she does not need to see that. I just added the delete of that data to the query and it looks good, thanks for all the help I received especially Andy. I am learning a lot.

Who says you cannot teach an old dog new tricks.

Cretin

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close