VBA adding a row (with a few values) to each workbook in a folder

V

Vexorg

Board Regular
Joined
Oct 5, 2010
Messages
116
Hi Guys,

The purpose of this is to avoid the 'Conversion Type Error' when importing into MS Access.

I need to add a row Above A2 to about 300 files (directory). In the added row I will also have to add the value 'Delete' to cells K2 and Q2. The ultimate goal is to not have to do this manually.

Is this possible?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Try this out.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Open_My_Files()<br><SPAN style="color:#00007F">Dim</SPAN> MyFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>MyPath = "M:\Access Files\" <SPAN style="color:#007F00">'Change Path</SPAN><br>MyFile = Dir(MyPath)<br><br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> MyFile <> ""<br><SPAN style="color:#00007F">If</SPAN> MyFile <SPAN style="color:#00007F">Like</SPAN> "*.xls" <SPAN style="color:#00007F">Then</SPAN><br>Workbooks.Open MyPath & MyFile<br>Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove <SPAN style="color:#007F00">'This inserts new row</SPAN><br>Range("K2,Q2") = "Delete" <SPAN style="color:#007F00">'Add text to cells</SPAN><br>ActiveWorkbook.Close <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>MyFile = Dir<br><SPAN style="color:#00007F">Loop</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
Click to expand...
 
Upvote 0
Hi Trevor, thank you for your post. This worked but the only problem is the column is being placed at the top of the sheet and I need it to be placed below the first row (because it contains column headers). See below:

<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>Before</caption> <thead> </thead> <tbody> <tr valign="TOP"> <td>Account</td> <td>Account Type</td> <td>Order Placer Name</td> <td>A2 general Account UsageCode</td> </tr> <tr valign="TOP"> <td>000J66513</td> <td>CASH</td> <td>
</td> <td>CUSTOMER</td> </tr> <tr valign="TOP"> <td>000J66513</td> <td>CASH</td> <td>
</td> <td>CUSTOMER</td> </tr> </tbody> <tfoot></tfoot> </table>
<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>After</caption> <thead> </thead> <tbody> <tr valign="TOP"> <td>Account</td> <td>Account Type</td> <td>Order Placer Name</td> <td>A2 general Account UsageCode</td> </tr> <tr valign="TOP"> <td>
</td> <td>DELETE
</td> <td>
</td> <td>DELETE
</td> </tr> <tr valign="TOP"> <td>000J66513</td> <td>CASH</td> <td>
</td> <td>CUSTOMER</td> </tr> </tbody> <tfoot></tfoot> </table>
Im not sure how to make make it skip a row.

<table bgcolor="#ffffff" border="1" cellspacing="0"><caption>Code Result</caption> <thead> <tr> <th bgcolor="#c0c0c0">
</th> <th bgcolor="#c0c0c0">
</th> <th bgcolor="#c0c0c0">
</th> <th bgcolor="#c0c0c0">
</th> </tr> </thead> <tbody> <tr valign="TOP"> <td>Account</td> <td>DELETE</td> <td>Order Placer Name</td> <td>DELETE</td> </tr> <tr valign="TOP"> <td>000J66513</td> <td>CASH</td> <td>
</td> <td>CUSTOMER</td> </tr> <tr valign="TOP"> <td>000J66513</td> <td>CASH</td> <td>
</td> <td>CUSTOMER</td> </tr> </tbody> <tfoot></tfoot> </table>
 
Upvote 0
Thats exactly what I Tried but that did not seem to work, I will run it again and see if I come up with anything.
 
Upvote 0
From your samples can you not just change row 1 headings to DELETE

Range("K1,Q1") = "Delete" 'Add text to cells


Why is necessary to Insert a Row, if the spreadsheet has headings in Row 1 ?
 
Upvote 0
Hey ok everything was fine, i changed the rows to 2:2, and its working. Thanks again guys.

I have one last question.. I can run this from excel but for some reason I cant run this in access..

When I run the code in access to receive an error that says 'Compile Error: Sub or Function not defined'. Is this occurs because access cannot open excel flies?

It then highlights 'rows':


Rich (BB code): 
Public Sub AddRows()
Dim MyFile As String
MyPath = "\\msad\root\NA\NY\LIB\fid\FIDSTP\Confirm Renov\Catch All\FCO DB QA\" 'Change Path
MyFile = Dir(MyPath)
Do While MyFile <> ""
If MyFile Like "*.xls" Then
Workbooks.Open MyPath & MyFile
Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove 'This inserts new row
Range("G2,AC2") = "Test300" 'Add text to cells
ActiveWorkbook.Close True
End If
MyFile = Dir
Loop
End Sub
 
Last edited:
Upvote 0
Pleased to read you have a working solution :)

Hi Jim hope you are well....;)
 
Upvote 0
Vexorg said:
Hey ok everything was fine, i changed the rows to 2:2, and its working. Thanks again guys.

I have one last question.. I can run this from excel but for some reason I cant run this in access..

When I run the code in access to receive an error that says 'Compile Error: Sub or Function not defined'. Is this occurs because access cannot open excel flies?

It then highlights 'rows':


Rich (BB code): 
Public Sub AddRows()
Dim MyFile As String
MyPath = "\\msad\root\NA\NY\LIB\fid\FIDSTP\Confirm Renov\Catch All\FCO DB QA\" 'Change Path
MyFile = Dir(MyPath)
Do While MyFile <> ""
If MyFile Like "*.xls" Then
Workbooks.Open MyPath & MyFile
Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove 'This inserts new row
Range("G2,AC2") = "Test300" 'Add text to cells
ActiveWorkbook.Close True
End If
MyFile = Dir
Loop
End Sub
Click to expand...

If you are running this from Access then you will have to set references to use Excel Code, to do this you would go into the VBA screen in your database select Tools Menu and Reference then scroll down until you find Microsoft Excel XX .object Library and click the box. This will allow you to then add the extras in to run this.

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> AddRows()<br><SPAN style="color:#007F00">'Make sure you have selected the Tools Menu and Reference</SPAN><br><SPAN style="color:#007F00">'Find Microsoft Excel and tick the box</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Excel.Application<br><SPAN style="color:#00007F">Set</SPAN> ws = CreateObject("Excel.Application")<br><SPAN style="color:#00007F">Dim</SPAN> MyFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MyPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><br>MyPath = "M:\Access Files\"  <SPAN style="color:#007F00">'Change Path</SPAN><br>MyFile = Dir(MyPath)<br><br><SPAN style="color:#00007F">With</SPAN> ws<br>    .Visible = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#007F00">' change to false if you don't want to see the motion</SPAN><br>MyFile = Dir(MyPath)<br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> MyFile <> ""<br><SPAN style="color:#00007F">If</SPAN> MyFile <SPAN style="color:#00007F">Like</SPAN> "*.xls" <SPAN style="color:#00007F">Then</SPAN><br>    .Workbooks.Open MyPath & MyFile<br>    .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove <SPAN style="color:#007F00">'This inserts new row</SPAN><br>    .Range("G2,AC2") = "Test300" <SPAN style="color:#007F00">'Add text to cells</SPAN><br>    .ActiveWorkbook.Close <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>MyFile = Dir<br><SPAN style="color:#00007F">Loop</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">Set</SPAN> ws = <SPAN style="color:#00007F">Nothing</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
VBA - Match two values from Table1 to two in Table2 and move/align
2
Replies
15
Views
268
Welshy1491
W
I
  • Question Question
Receive Automation error when running code & values added to sheet twice
2
Replies
13
Views
445
NoSparks
N
H
  • Question Question
'How to' question about a Dashboard
Replies
5
Views
660
etaf
etaf
S
  • Solved
For each row, create new folder based on single cell value, then save exported XML doc to matching folder.
Replies
3
Views
688
shand
S
J
  • Question Question
Insert Row based on cell Value VBA
Replies
2
Views
276
mumps
mumps

Forum statistics

Threads
1,224,545
Messages
6,179,432
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top