Automating task in Excel using VBA coding

[Lou_Reed]

In the two zipped files attached are a db that I have been working on. One is in the original form and the other is the modified form.

It order to port the spreadsheet over to MS access it must be prepared. One thing is

1. to create or insert a new column for column A, and then number it starting a row 2 from 1 to 130. This column goes to the leftmost point and
fills all the new cells in that column with a sequence of numbers down the end of the spreadsheet.



The other thing is 2

2. to place the word "text" in the Gate 22 column on the second row. At present I do this by hand. The sponsor would like it automated.

I assume that a button could be placed on the spreadsheet that, but I am not sure how to VBA code it. Any help appreciated.

Thanks in advance.

Respectfully,

Lou Reed

[ranman256]

import (or link) the excel sheet in access.
build a query to fit your requirements,
field would be row#
put the word '"text" where you need it
and all the other fields.

then use that query to append the data to your internal table. (an import)
If your record# is required starting at #1 everytime, then run a quick macro in excel to number the rows. THEN import.

[Lou_Reed]

I appreciate the answer, but I believe that the sponsor would like to have a button on the Excel side of things that I can press or click and it will create the new column, sequence it with numbers 1-130 and add "text" to the second row under Gate 23. This is all in preparation for importing the file into Access. I really must do this in Excel. Anything else will not work since the sponsor want this way that I outlined.

I really do not and I am sure the sponsor really does not want to link Access and Excel. Import yes but only after the Excel sheet has been prepared.

This must all be done before importing the Excel spreadsheet into MS Access. It cannot be done after.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[ssanfu]

Don't know why Excel questions are coming up in an Access forum........


Start by clicking on the cell where you want the word "TEXT". Then, in the menu, click on DATA, Name Manager.
Add a new named range; it should be named "Gate_22".


Quick navigate


Modify the code for the sub AddColumn:

Sub AddColumn()
    Dim X As Integer

    'add column
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight

    'number the rows
    For X = 2 To 130
        Range("A" & X).Value = X - 1
    Next X

    'delete rows   ???
    Rows("131:140").Select
    Selection.Delete Shift:=xlUp

    'put the word text in the Gate 22 column, row 2
    Range("Gate_22").Value = "text"

    'move to top left
    Range("A2").Select
    ActiveCell.Offset(-1, 0).Select

End Sub

Attached is the workbook if you need an example.....

[Lou_Reed]

They are coming up because my sponsor wants things to be more automated.

I know it sounds crazy to ask a bunch of engineers to use only highly automated spreadsheets instead of semi-automated ones. They are engineers after all.

But, I am trying to automate the preparation of an Excel spreadsheet to be exported to MS Access. The end game here MS Access and getting a spreadsheet to import into Access with minimum difficulty.

The series of buttons that I have previously created do not automate the process enough. I have been told. They work, but I must automate more.

I have to have a button that adds a new column to the spreadsheet (although that can be done by hand), puts a sequence of numbers in it (again this can be done by hand) and finally puts the word "text" in the second row below Gate 22 on the spreadsheet (which can also be one by hand). This is goofy, but this is what they want and I will give it to them.

I am trying to build some VBA code in Excel to do this. It is just to prepare the Excel spreadsheet before it is exported to Access. If the spreadsheet is not prepared and exported in original form it will generate hundreds of errors. No one wants that.

I know how to do most of it. I am just unsure as to how to code (in VBA) so as to fill the newly created column with sequential number starting a the second row and going down from 1 at the top to 130 at the bottom. That is my only questions. I know it can be done with a loop. I just d not know the syntax.

Ant help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[ssanfu]

I was just musing the fact that you are asking EXCEL questions in an ACCESS forum. Hmmmmm Just talking to myself... no one else listens..


Having said that, I explained how to do what you want in Post #4 AND I included the modified spreadsheet.
Adding the word "TEXT" only took one line of code and a named range object.
(the other two lines were to get back to cell A1.....)

Good luck with your ... engineers..

[Lou_Reed]

I thank you for all of your trouble. I know it sounds goofy. But this is what they want. They are paying for this and I will give it to them.

They must assume that the MS Office ability of their engineers is very low. So much of this can be done by hand - easily.

I will try what you said. Again thanks for you concern and trouble.

Respectfully,

Lou Reed

[Lou_Reed]

The whole thing compiled except for the line shown. I know that Gate_22 is not the column name it is something like A-Z.
Of course, the second part is 2 signifying row.

Is that correct?

Respectfully,

Lou Reed


Range("Gate_22").Value = "text"

[Lou_Reed]

In the zip file attached that I copied from you code, the program seems to hang on one of the last lines. The one with Gate_22 in it.

It can be seen in the two screen capture zip files.

In your code it worked very well. In my it crashes and hangs on the line.

What is causing this?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

Here is another example of what I am talking about. The code hangs on Gate_22 and gives a messages that I sent in the attached screen capture zip file.

What is causing this?

Any help appreciated.

Respectfully,

Lou Reed

[Lou_Reed]

Please I need an answer to this post. The VBA code in Post #4 simply throws a run-time error occurs when I click on he button to add a column and a seq of numbers in it. I am stuck, why will this code not work?

Respectfully,

Lou Reed

[ssanfu]

Your posted workbook worked perfectly for me... NO errors..... AFTER I ADDED THE NAMED RANGE. (which you didn't do.)




I demonstrated how to add a named range in Post #4, but here it is again.

1) Click on the cell where you want to add the word "TEXT". It should be cell AS2.
2) In the menu, click on "Formulas".
3) Click on "Name Manager"
4) In the dialog pane, click on "NEW"
5) In the "New Name" dialog box, the "Name" text box should be "Gate_22". Access automatically named the range.
You can name it anything you want it to be; it could be named "Bananas", "LouReed", "Mouse", "BitCoin", "XXX", etc.
"Gate_22" seems like the best name to me.

6) In the "Refers to" box, you should see "='Production Tracker'!$AS$2". (This is the cell location where you want to add the word "TEXT".)
7) Click OK.
8) Click "Close"

Click on cell A2.



To check that the Named Range has been created correctly:


You should see:



If everything worked correctly, click on cell A2.
Click SAVE (save the workbook)

Now click the "Add Column" button.