Deleting access rows 1

[Cretin]

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

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

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

 

[Andrzejek]

Before [tt]DoCmd.TransferSpreadsheet [/tt] 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.

 

[Cretin]

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

Cretin

 

[Andrzejek]

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.

 

[Cretin]

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

Yes

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

No

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

Do you know how many rows you need to delete?

No

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

 

[Andrzejek]

Let's say in column [tt]I[/tt] 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:

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

blnKeepGoing = True[green]
'Assuming first row is a header row[/green]
L = 2

Set xlApp = CreateObject("Excel.Application")

With xlApp
    .Visible = True
    .Workbooks.Open FileName:="[red]C:\TEMP\MyFile.xlsx[/red]"
    
    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.

 

[Cretin]

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

Set x1App = CreateObject("Excel.application")

With x1App
   .Visible = True
 [b].Workbooks.Open FileName:="U:\Projects\timetracker\testing\ann_Timesheet_20160806.xlsx" [/b]
 .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

 

[Cretin]

now I am getting object required on the bolded section:

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

Cretin

 

[Cretin]

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.

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

 

[Cretin]

Based on debug it is skipping the bolded line

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
    [bold] .Rows(L & ":" & L + 5).Delete [/bold]
    binkeepgoing = False
    End If
   
   L = L + 1
  Loop

Cretin

 

[Andrzejek]

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

This line of code:[tt]
If InStr(UCase(.Range("A" & L).Value), "Project") > 0 Then[/tt]
looks for a word "PROJECT" (not "insert")

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Cretin]

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

.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

 

[Andrzejek]

OK, change this line to:

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

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

[pre]
A
1 AbcD
2 1234[blue]
3 Some PrOjeCt Here
4 Something[/blue]
[/pre]
This code should Delete [blue]BLUE[/blue] rows.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Cretin]

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

 

[Andrzejek]

Assuming this text is in the column A, try:

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.

 

[Cretin]

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

 

[Andrzejek]

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.

 

[Cretin]

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

 

[kjv1611]

Cretin,

Glad you got your problem sorted out or mostly worked out, great that you hung in there till the end. Some suggestions:
[ul]
[li]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.[/li]
[li]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.[/li]
[li]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.[/li]
[li]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.[/li]
[/ul]

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

 

[Cretin]

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