Copy/paste from Excel to a password protected intranet application

[Zakynthos]

I would like to copy cells (one at a time) from Excel to a resource
management program that is password protected on my company intranet. It is
not possible to paste columns in one go, so it must be done on a copy
cell/paste to cell repeat basis.

I've tried running the following code which I've seen from reading another
question but it errors at the Get application point.

Given the protection on the destination program, can this be done and if so,
how?

The code I tried unsuccessfully was:

CombineFiles()
Range("A1").Select
MyBook = ActiveWorkbook.Name
MyTargetCell = ActiveCell.Address
MySource = Application.GetOpenFilename
Workbooks.Open Filename:=MySource
Range("A1").Select
Set myRange = Range(Selection, ActiveCell.SpecialCells(xlCellTypeLastCell))
'<-Solution : Take out the select here because you are setting the myRange
with this line
myRange.Copy
Application.DisplayAlerts = False '<-- This will save you from having Excel
notify you that you placed a large amount of info on the clipboard before
closing the source file
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks(MyBook).Activate
Range(MyTargetCell).Select
ActiveSheet.Paste
ActiveWorkbook.Save 'SaveAs MySource <-- this is saving the Activeworkbook
as the Source workbook (I don't think that is what you want to do?)


Many thanks for your help!

 

[Tom Ogilvy]

You don't show any Get application line of code. Your code appears to be
pasting from one workbook to another workbook. I don't see any interaction
with another program.

 

[Zakynthos]

Thanks for letting me know.

So how, and at what point, would I:

(a) write in the Get Application code

(b) override the need to enter a password as the macro will obviously stop
running at this point.

Any specific advice would be much appreciated.

Many thanks

 

[Tim Williams]

Can you log in to the intranet application first, before running the
copy/paste?

A few more details might be useful. You don't mention what type of
application you're trying to interact with: I has assumed from
"intranet" that it was web-based, but your code sample points towards
an Excel-based app.


Tim.

 

[Zakynthos]

I think I mistakenly wrote 'intranet' when I really meant 'network-based'.
The software is a resource management program called Vantage Point, which is
password protected.

The code sample I used was from a question about pasting from one excel
workbook to another application (I think MS Access) which was password
protected, so I thought this might be a good starting point.

However, as the Vantage Point software does not permit block pasting of
cells, I would need to run a macro which would:

1. Copy a single cell from data in Excel
2. Launch the Vantage Point password-protected resource management program.
3. Paste the data from Excel into a single cell in the VP spreadsheet
4. Return to Excel, copy the next cell ........ and so on

I have tried inserting Vantage Point as an Object within Excel and creating
2 macros:

1. To copy a data cell from Excel and open the Vantage Point application
2 To enter a password in the login window of Vantage Poiint
3. Then run the first macro to include running the second macro.

This doesn't work! it opens multiple logon windows, at least 20, eventually
the password is auto entered and then the computer crashes!!!!

Any suggestions for a Visual Basic script that will do the job?

Many thanks for your help!

Tony

 

[Tim Williams]

So the VP application is Excel-based? Does it support automation
(createobject, etc)?
Can you post a URL to the vendor's website?
Are the values you're trying to paste in all going into the same screen in
VP, or does each value get pasted to a different screen?
Have you tried logging in first and then running the Macro?

 

[Zakynthos]

I assume that Vantage Point is 'Excel-based', as it contains spreadsheets
I don't know whether it supports automation - certainlly, I can insert a VP
icon into an Excel spreadsheet and logon from there.
I have tried logging on first and attempting to create a macro, by:

1. Inserting VP as object in the Excel ss.
2.Creating a macro to open VP from this object and enter password
3. Switch to VP ss window .....
4. Paste in data into appropriate cell
5. Repeat with next value

On running the macro it stops at the VP logon screen and asks for password

I have also tried opening VP first and creating a similar macro in Excel to
copy/paste between the 2 windows but on running it it only copies the Excel
cell(s)

This is the code generated:

Application.Goto Reference:="R1C4"
Application.CutCopyMode = False
Selection.Copy

The application is installed on the company network not the intranet so I
cannot post a URL to the vendor's website.

Yes, the values I'm trying to paste in all go into the same screen, but only
one cell at a time in the VP ss, i.e copy one Excel cell, paste one cell in
VP.

Regards,

Tony

 

[Tim Williams]

Tony,

If you look at the WindowsTask Manager while the VP app is running what do
you see - does it show up as Excel or as a different app ?

By logging in first I meant your second approach - ie. log in manually then
run the macro to copy the data. Since logging in is something you only do
once then skipping automation of this step is going to make everything
simpler.

Since this thread is getting a bit drawn-out now, feel free to follow up via
e-mail - you can reach me at timjwilliams using the domain gmail.com

Tim.