Dynamically add and run a vba code

Question

The followings are my current steps

Access file exports a CSV file by Macro (VBA code)
The exported CSV file get modified by Macro (VBA code)

Right now, I'm executing the macro on Access (step 1)-> under the exported file, add the code and run (step 2)

I'd like to simplify the process.

Is it possible to by doing step 1, the step 2 code get added to the csv file and run?

Step 1 code

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intFile As Integer
Dim strFilePath As String
Dim intCount As Integer
Dim strHold    
strFilePath = "C:\temp\TEST.csv"    
Set dbs = CurrentDb    
Set rst = dbs.OpenRecordset("Import", dbOpenForwardOnly)    
intFile = FreeFile
Open strFilePath For Output As #intFile    
Do Until rst.EOF
   For intCount = 0 To rst.Fields.Count - 1
   strHold = strHold & rst(intCount).Value & "|"
   Next
   If Right(strHold, 1) = "|" Then
      strHold = Left(strHold, Len(strHold) - 1)
   End If
   Print #intFile, strHold
   rst.MoveNext
   strHold = vbNullString
Loop    
Close intFile    
rst.Close    
Set rst = Nothing        
End Function

Step 2 code

Sub deleterows()
lastrow = Cells(Rows.Count, 4).End(xlUp).Row
For i = lastrow To 1 Step -1
    If Cells(i, 4).Value < Date Then Rows(i).EntireRow.Delete
Next i
End Sub

note I prefer not to use windows scheduler to run Macro at a certain time.

My good reference has been so far Is it possible to automatically input VBA code when a new sheet is generated? & Dynamically insert macro into a new excel workbook & https://support.microsoft.com/en-us/kb/219905

I will try my best to be responsive! Please leave a comment for clarification. Thank you!

(a) You can't have VBA in a CSV file - a CSV file is a simple text file. (b) Instead of deleting rows via a macro, why don't you just not bother writing those records to the original CSV file? (c) I just noticed that your file isn't a CSV file anyway, it's a pipe-delimited file with a CSV extension. — YowE3K
– YowE3K, Commented Jan 4, 2017 at 19:45
@YowE3K thanks for the comment! Since It's a SQL server that is connected with Access, It's impossible for me to edit it. — John Tipton
– John Tipton, Commented Jan 4, 2017 at 19:47
@YowE3K to follow-up (a), I believe I can run a vba code on the csv file. As I have deleted unnecessary rows through vba code. — John Tipton
– John Tipton, Commented Jan 4, 2017 at 19:48
@YowE3K Maybe I can code to export only those rows that I want to export? — John Tipton
– John Tipton, Commented Jan 4, 2017 at 19:49

YowE3K · Accepted Answer · 2017-01-04 20:05:02Z

1

You could simply change your existing Step 1 code to be:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intFile As Integer
Dim strFilePath As String
Dim intCount As Integer
Dim strHold    
strFilePath = "C:\temp\TEST.csv"    
Set dbs = CurrentDb    
Set rst = dbs.OpenRecordset("Import", dbOpenForwardOnly)    
intFile = FreeFile
Open strFilePath For Output As #intFile    
Do Until rst.EOF
   'Check the 4th field to see whether it is today or later
   If CDate(rst(3)) >= Date Then
       'If so, create a record (if not, don't)
       For intCount = 0 To rst.Fields.Count - 1
           strHold = strHold & rst(intCount).Value & "|"
       Next
       If Right(strHold, 1) = "|" Then
           strHold = Left(strHold, Len(strHold) - 1)
       End If
       Print #intFile, strHold
   End If
   rst.MoveNext
   strHold = vbNullString
Loop    
Close intFile    
rst.Close    
Set rst = Nothing        
End Function

edited Jan 4, 2017 at 20:05

answered Jan 4, 2017 at 19:51

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Tipton Over a year ago

So you have edited to CDate(rst(3)) >= Date! I see what you are doing there! Thanks will let you know how it works!

John Tipton Over a year ago

Thank you so much! You are a genius! :) You found a much simpler way to solve it! Thank you again

YowE3K Over a year ago

Just check whether the downstream processes are expecting comma or pipe as the delimiter. My guess is that your old method was saving the file out of excel as a true comma-separated values file, while Access was saving it as pipe-delimited. (You may need to change the "|" to "," in the code I posted.)

Collectives™ on Stack Overflow

Dynamically add and run a vba code

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related