Tranferring VBA used in Access to Word and Excel

[Mike375]

I am OK with VBA thats is used for file copying, renaming etc that I launch from Access and using the same in a Word macro. I assume Excel would be the same, although I don't know where I would put it

However, today I sold what I consider a nice back up system and the buyer also thought it was nice. But a friend of the buyer who was present asked could I make it in Excel or Word as they did not have MS Office Professional.

The problem is that the code is setting the value of fields on a form and using that data to form part of the file name (date/time). There is a whole lot of the following type of thing



Forms!CodeBUSystem!Current = ("Viper " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss"))

Forms!CodeBUSystem!Previous = Forms!CodeBUSystem!Current

FileCopy "C:\ViperResides\Viper.mdb", "\\MikeHP\ViperDaily\" & Format([Forms]![CodeBUSystem]![Current]) + ".mdb"

FileCopy "\\MikeHP\ViperDaily\" & Format([Forms]![CodeBUSystem]![Previous]) + ".mdb", "\\MikeHP\ViperArchive\" & Format([Forms]![CodeBUSystem]![Previous]) + ".mdb"

Kill "\\MikeHP\ViperDaily\" & Format([Forms]![CodeBUSystem]![Previous]) + ".mdb"



If this was launched from Excel would I use Cells to replace Form/Textbox references?

What about Word. Could something like this be done in Word.

Also, from Access I have it open a tabular form and as it completes each copy then it creates a new record and inserts the message etc. In fact the on screen presentation why the main reason I moved from doing this sort of thing in batch files. Could something like that be done using Excel, perhaps the "copies xyx to wheverer" could be inserted into Cells. What about Word, perhaps bookmarks.

 

[namliam]

I am OK with VBA thats is used for file copying, renaming etc that I launch from Access and using the same in a Word macro. I assume Excel would be the same, although I don't know where I would put it

Hit ALT+F11, just like in Access

However, today I sold what I consider a nice back up system and the buyer also thought it was nice. But a friend of the buyer who was present asked could I make it in Excel or Word as they did not have MS Office Professional.

Guess that means I have some commision comming???

If this was launched from Excel would I use Cells to replace Form/Textbox references?

Cells or Range and Activecell

What about Word. Could something like this be done in Word.

Nothing is impossible but... I would go with Excel if I had to choose Excel vs Word.

My bankaccount is looking forward to your response

 

[chergh]

I'd go with a kick in the balls rather than choose word

 

[namliam]

I'd go with a kick in the balls rather than choose word

*OUCH*

You sure about that one??? hahahahahaha

 

[Mike375]

You were sounding board

I suppose there is no point in fiddling with Word as anyone who has Word will have Excel.

By the way, I could not get the error handling to work so I just put one of these in a module (for each disk) and the On Error Resume Next before the Call

FileCopy "C:\ViperResides\Viper.mdb", "\\MikeHP\ViperDaily\" & Format([Forms]![CodeBUSystem]![Current]) + ".mdb"

Forms!CodeBUSystem!CompOn = 1

So if computer is not on [CompOn] stays at 0 so then I could do my messages with IF

I will go to work on Excel. I just wanted to know that I would not be going down a long road that is a dead end.

 

[chergh]

Word VBA really is a travesty. I was doing a script once to take the contents of a word document and format it into excel so it could be uploaded to a database for use in an application. The easiest way to work with the document was to create a huge text string of the entire document and then work with the string. Even returning a paragraph number isn't simple in word.

ActiveDocument.Range(0, Selection.Paragraphs(1).Range.End).Paragraphs.Count

Honestly I really hope I never need to go near the bloody thing again.

 

[namliam]

Word VBA is 'kinda' unfriendly... but that might be because we dont actively use it....

You can handle this in excel, as long as you dont have > 65k files/copies.
Activecell.Cells(1,2).select (air code so verify)
will select the cell one row down
you can physicaly select it or just write data to it (droppng the select)

The later is faster,but you will lose the progress as it drops off the screen.

You were sounding board

I was what?? LOL
You might have spend another month trying to get it to work without online help from me or anyone else...
Though that would be true for some things I did too..

 

[Mike375]

I just made a macro in Excel. Exactly the same as Word and also put a function in Excel. Progress is being made

I don't know if I travel on a different road to others but I rarely come across anyone who uses Access, always only Excel and Word. A few people might have made a table or two and then stopped. I am doing something with a small real estate office at the moment and their figures for the current sales/rentals etc are stored in an Excel file.....and one Excel file for each month. The fellow that owns this business is just amazed that this can be put a single file etc.

My guess is that if the road from knowing zero to be the town guru is 10 miles then with Word you only need to travel a 1/4 mile down that road to be able to do most things that most people want to do with Word and maybe it is 2 miles with Excel. But Access requires you to get a fair way down the road before you can even make something simple that works.

 

[Singh400]

I just made a macro in Excel. Exactly the same as Word and also put a function in Excel. Progress is being made

I don't know if I travel on a different road to others but I rarely come across anyone who uses Access, always only Excel and Word. A few people might have made a table or two and then stopped. I am doing something with a small real estate office at the moment and their figures for the current sales/rentals etc are stored in an Excel file.....and one Excel file for each month. The fellow that owns this business is just amazed that this can be put a single file etc.

My guess is that if the road from knowing zero to be the town guru is 10 miles then with Word you only need to travel a 1/4 mile down that road to be able to do most things that most people want to do with Word and maybe it is 2 miles with Excel. But Access requires you to get a fair way down the road before you can even make something simple that works.

Analogy overload anyone?

 

[chergh]

When I read the town guru bit the saying "in the kingdom of the blind the one eyed man is king" cam to mind.

 

[namliam]

My guess is that if the road from knowing zero to be the town guru is 10 miles then with Word you only need to travel a 1/4 mile down that road to be able to do most things that most people want to do with Word and maybe it is 2 miles with Excel. But Access requires you to get a fair way down the road before you can even make something simple that works.

I would say that is a fair analogy.... Access is definatly a step or 2-3 beyond excel.

Guess I could make a killing down under

 

[Mike375]

Guess I could make a killing down under

Come on down

You can make the stuff and I will get the jobs.

Actually I am gradually moving out of insurance and doing this stuff. So far I have one salesman who goes for the whole week and I do one day a week on canvassing.

 

[jal]

I'm guessing the following is true. In Access VBA, there is no "UserForm" - it's not called "UserForm" in Access, contrary to Word, Excel, and Outlook.

I'm guessing, then, that Outlook, Excel, and Word VBA are very similar. Access VBA is in a class of its own. I have dabbled in Outlook VBA, so maybe I can help a bit.

Here's my first suggestion. To make it easy for the user to launch a UserForm, create a button on the toolbar using these steps:

Create a new macro and use the following as your macro code, assuming your user form was named userForm1

Sub Macro1()
Dim f As New UserForm1
f.Show
End Sub


To add it to the toolbar, rightclick the toolbar > Customize > Commands Tab and scroll down to select Macros. This should list your macros in the right pane such as
Project1.Macro1
Now drag this name onto the toolbar

I don't think that the Forms! collection is available with UserForms. You'll have to probably use textbox1.Value and so forth.


The JET engines are free, so you can access a database if needed (create it in Access if you like, but the user won't need Access to levarage it). Takes a bit more code to access a database from UserForm VBA, but it's not too bad.

The code for populating listboxes and comboboxes is generally different in UserForm VBA than Access VBA.

 

[Mike375]

jal,

Thanks for that but I think that is a bit far down the track for me at them moment.

Mailman

Yours worked and put it in the cell next to the active cell.

ActiveCell.Cells(1, 2) = ("Viper " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss"))

And this went to cell C10

Range("C10:C10") = ("Viper " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss"))

Placed this in the relevant cell

Viper 2008-12-07 00-28-09

And it referenced OK with =(C10) in another cell

And this copied it across the network. I thought I would be adventurous and try for across the netwok first

FileCopy "C:\ViperResides\Viper.mdb", "\\MikeHP\ViperDaily\" & Format(Range("C10:C10")) + ".mdb"

And putting this in the one macro made it a "live" copy

Range("C10:C10") = ("Viper " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss"))
FileCopy "C:\ViperResides\Viper.mdb", "\\MikeHP\ViperDaily\" & Format(Range("C10:C10")) + ".mdb"

 

[namliam]

Range("C10:C10") = ("Viper " + Format(Now, "YYYY-MM-DD") + " " & Format(Now, "hh-mm-ss"))

This denotes a range of one cell... to save yourself some headaches...
Range("C10") does the same thing.

 

[Mike375]

This denotes a range of one cell... to save yourself some headaches...
Range("C10") does the same thing.

I guessed it was probably redundant when I was I doing it.

The next thing I need to do is learn more about where this can be launched from. I know in Word and assume Excel is the same that you can put the macro up on the toolbar but that seems a bit limited.

Does Excel have the situation whereby (like a tabular form in Access) you click on the row and then what happened was information in a cell (s) was used from that row?

I imagine that is would be possible to put the output that is going to different cells on a different worksheet so it does land all over the working screen.

 

[Mike375]

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)does the job

But since you can only have one Sub Worksheet_SelectionChange on the worksheet then to get the different cells I did If/Then for each required cell.