Word Log

[bryVA]

I have a form automation program that will take data from a userform and populate it in bookmarks. I would like to be able to take that same data and populate that data in an excel spreadsheet that is saved in the MyDocuments Folder. The following is the code I have so far. I am having error messages with this code saying The Remote server Machine does not exist and every once in a while it gives me an object error.

Dim sTmp As String
Dim wshShell
Set wshShell = CreateObject("WScript.Shell")
sTmp = wshShell.SpecialFolders("MyDocuments") & Application.PathSeparator
strFPath = ThisDocument.Path & Application.PathSeparator
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
On Error GoTo Createnew
Set xlWB = xlApp.Workbooks(sTmp & "log119.xls")
On Error GoTo 0
xlWB.Sheets("Sheet1").Activate
restart:
xlWB.Sheets("Sheet1").Range("A1").Select
Do Until ActiveCell.Offset(0, 1).Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = ROS1
ActiveCell.Offset(0, 1).Value = TextBox53.Value
ActiveCell.Offset(0, 2).Value = CmbStation
ActiveCell.Offset(0, 3).Value = TextBox54.Value
ActiveCell.Offset(0, 4).Value = TextBox54.Value
ActiveCell.Offset(0, 5).Value = Format(Now(), "MMMM DD YYYY")
ActiveCell.Offset(0, 6).Value = Format(Now(), "MM/DD/YYYY")
ActiveCell.Offset(0, 7).Value = TextBox64.Value
ActiveCell.Offset(0, 8).Value = TextBox57.Value
ActiveCell.Offset(0, 9).Value = TextBox62.Value
ActiveCell.Offset(0, 10).Value = TextBox63.Value
ActiveCell.Offset(0, 11).Value = "TELEPHONE"
ActiveCell.Offset(0, 12).Value = Persons
ActiveCell.Offset(0, 13).Value = vAddr1
ActiveCell.Offset(0, 14).Value = vTele
ActiveCell.Offset(0, 15).Value = TextBox8.Value
ActiveCell.Offset(0, 16).Value = TextBox9.Value
ActiveCell.Offset(0, 17).Value = TextBox10.Value
ActiveCell.Offset(0, 18).Value = TextBox11.Value
ActiveCell.Offset(0, 19).Value = TextBox18.Value
ActiveCell.Offset(0, 20).Value = TextBox12.Value
ActiveCell.Offset(0, 21).Value = TextBox13.Value
ActiveCell.Offset(0, 22).Value = TextBox14.Value
ActiveCell.Offset(0, 23).Value = TextBox15.Value
ActiveCell.Offset(0, 24).Value = TextBox19.Value
ActiveCell.Offset(0, 25).Value = TextBox75.Value
ActiveCell.Offset(0, 26).Value = TextBox16.Value
ActiveCell.Offset(0, 27).Value = TextBox17.Value
ActiveCell.Offset(0, 28).Value = TextBox23.Value
ActiveCell.Offset(0, 29).Value = TextBox70.Value
ActiveCell.Offset(0, 30).Value = TextBox26.Value
ActiveCell.Offset(0, 31).Value = TextBox71.Value
ActiveCell.Offset(0, 32).Value = TextBox66.Value
ActiveCell.Offset(0, 33).Value = TextBox67.Value
ActiveCell.Offset(0, 34).Value = TextBox68.Value
ActiveCell.Offset(0, 35).Value = TextBox69.Value
ActiveCell.Offset(0, 36).Value = TextBox76.Value
ActiveCell.Offset(0, 37).Value = TextBox77.Value
ActiveCell.Offset(0, 38).Value = TextBox78.Value
ActiveCell.Offset(0, 39).Value = TextBox79.Value
ActiveCell.Offset(0, 40).Value = TextBox80.Value
ActiveCell.Offset(0, 41).Value = TextBox81.Value
ActiveCell.Offset(0, 42).Value = TextBox82.Value
ActiveCell.Offset(0, 43).Value = TextBox83.Value
ActiveCell.Offset(0, 44).Value = TextBox84.Value
xlWB.SaveAs (sTmp & "Log119.xls")

'ActiveWorkbook.Save
xlWB.Close False ' close the workbook without saving
xlApp.Quit ' close the Excel application
Set xlWB = Nothing
Set xlApp = Nothing

Any help would be very much appreciated. : pray2:

Thanks,

[lucas]

Maybe instead of using shell you could set a reference to the Excel object library and use workbooks.open:

Set appExcel = CreateObject("Excel.Application")
'Change the file path to match the location of your test.xls
Set objSheet = appExcel.workbooks.Open("c:\temp\test.xls").Sheets("Sheet1")

On my computer the path to my documents looks like this:

C:\Users\Steve\Documents

[macropod]

Hi bryVA,

Your code would also be much more efficient if you replaced:
On Error Goto 0
xlWB.Sheets("Sheet1").Activate
restart:
xlWB.Sheets("Sheet1").Range("A1").Select
Do Until ActiveCell.Offset(0, 1).Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = ROS1
ActiveCell.Offset(0, 1).Value = TextBox53.Value

...

xlWB.SaveAs (sTmp & "Log119.xls")
with:
Dim RngStart As Range

...

On Error GoTo 0
With xlWB.Sheets("Sheet1")
restart:
Set RngStart = .Cells(.Cells.SpecialCells(xlCellTypeLastCell).Row, 1)
Set RngStart = RngStart.Offset(1, 0)
With RngStart
.Value = ROS1
.Offset(0, 1).Value = TextBox53.Value

...

End With
End With
xlWB.SaveAs (sTmp & "Log119.xls")

[bryVA]

Thank you Macropod. I am still a novice at this. I figured out how to do this however I am also interacting with another program. I want it to activate an application that is running. I use the AppActivate however if the application has been minimized it will not bring up the application. Why doesn't it activate the program when I use AppActivate when it is minimized? The application I want to come up is a executable program.

Thanks for your help,

[macropod]

Hi bryVA,

From the vba Help file:

The AppActivate statement changes the focus to the named application or window but does not affect whether it is maximized or minimized.

I'm not an expert in this area, but you may be able to achieve what you're after with something like:

Sub Test()
Dim myTask As Task, myApp As String
myApp = "Microsoft Excel"
For Each myTask In Tasks
If InStr(myTask.Name, myApp) > 0 Then
With myTask
.Activate
.WindowState = wdWindowStateMaximize
' Do stuff here
.Close
End With
End If
Next myTask
End Sub

[bryVA]

Thank you, macropod. I tested this out but it gives me a Run-Time error 429 which states ActiveX Component can't create object. It stops at the following line of code:

For Each myTask In Tasks

What do I need to do to fix this. Do I need to reference something?

Thanks for all your help,

[macropod]

Hi bryVA,

Did you run the code as I posted it?

[bryVA]

Yes I Copied and pasted it in and it gives me that error. Is it suppose to be in a module or some place else?

Thanks for all your help,

Bryan

[macropod]

Hi bryVA,

That's odd because the implementation is almost the same as the vba Help file example for the Activate Method. As coded, the sub shouldn't do anything unless MS Excel is running (in which case it'd be activated, maximised, then closed).

A fuller demo of the code, which you should be able to run from a standard vba module, is:

Sub Test()
Dim myTask As Task, myApp As String
Shell "C:\Program Files\Microsoft Office\Office\Excel.exe", 1 ' Run Microsoft Excel.
myApp = "Microsoft Excel"
For Each myTask In Tasks
If InStr(myTask.Name, myApp) > 0 Then
With myTask
.Activate
.WindowState = wdWindowStateMaximize
MsgBox "Excel will now close"
.Close
End With
End If
Next myTask
End Sub
You may need to change the Excel.exe path for your system.

[lucas]

The code in post #5 works error free for me. If there is no Excel file open it does nothing, if there is one open it activates, maximizes it and closes it.

What version of office. I tested it on 2003

[bryVA]

Sorry MacroPod. I was making a mistake when I placed this in. It does work. Is there a way to make it so a database called SHARE can do the same thing as excel maximizing when Minimized? I have tried to adjust the code but it I could not get it to work.

Thanks for all your help. Thanks Lucas I rechecked what I did when you posted. I am running 2000 still.

[macropod]

Hi bryVA,

As I said in my previous post, I'm not an expert in this area, and I really can't help you further on that front. Perhaps another contributor can.