Free Microsoft Excel 2013 Quick Reference

Using IE from VBA - Permission Denied

Here is my code.

Dim ie As InternetExplorer
Dim ipf As Object

Set ie = CreateObject("InternetExplorer.Application")


Post your answer or comment

comments powered by Disqus
Sounds like a permissions problem. Why not create the temp file in the
users temp directory? that is what it is for.

? environ("Temp")
C:DOCUME~1ogilvtwLOCALS~1Temp

--
Regards,
Tom Ogilvy

"APutorek" > wrote in message
...
> I have an Excel Workbook with several macros and functions. One of the
functions creates a temp file on the local hard drive, I pull some data from
that temp file and email it to a user and then close the file and delete it.
This process works great when I'm testing on my system. This workbook
however is accessed through our intranet and when it accessed through IE and
the macros are run the temp file is created with no problem but I receive
the Permission Denied Error 70 when it gets to the line to delete the temp
file. I run Office 2K SP3, the systems in my office are all W2K SP4 and XP
Pro SP1. Everyone has IE 6 installed. I use the FileSystemObeject to
create the file and the kill method to try and delete it, I have also tried
the DeleteFile and DeleteFolder method. What am I doing wrong? Any help
would be great

Hi All,

Can you anyone fix this issue asap...

I developed few IE automation(VBA) using excel 2007 in my office. Now the IE automation is not working. In office, IT team has upgraded few changes in the software and It is displaying an error as Permission denied or Permission to use object defined.

Below code is for your reference,
IE.document.frames("TargetContent").document.all.Item("ID Name").Value = username

I need a quick help to resolve this issue, Thanks

Hello again

I'm having a small issue with the .additem command. My combobox listfillrange is a named range and when i try to use the .additem command, it gives me a "Permission Denied". But when I try the same with a new combobox, it works just fine.

combobox1.additem "item1"

The above code runns from another combobox. Both comboboxes are in the first sheet

Please Help

Is it possible to download file unknown from website(IE) in vba?

Hi all ,
I have two PCs on a network - one with windows vista and another with windows xp operating system. I dont know much about networks and all i know is that if i go to 'Network' on my windows vista taskbar - then i can see 'Netgear DG834GT Router' and 'Rashid-PC' (the pc with vista) and 'ROB-COMPUTER' (the pc with windows xp). I dont know how the network was setup but it has something to do with the modem (Netgear DG834GT Router) being connected to both PCs via wires (the router is used for connecting to the internet). When i open the ROB-COMPUTER from the Network window - all i see is the 'Printer' Icon, 'Printers' Icon, 'SharedDocs' Icon, C drive and D drive..

Im a coding in Excel 2007 and on the PC with windows vista. Basically I am trying the following code but its not working:


	VB:
	
 ' reference. Dim as Object causes late binding.
Dim ExcelSheet As Object 
Set ExcelSheet = CreateObject("Excel.Sheet", "ROB-COMPUTER") 
 ' Make Excel visible through the Application object.
ExcelSheet.Application.Visible = True 
 ' Place some text in the first cell of the sheet.
ExcelSheet.Application.Cells(1, 1).Value = "This is column A, row 1" 
 ' Save the sheet to C:test.xls directory.
ExcelSheet.SaveAs "C:TEST.XLS" 
 ' Close Excel with the Quit method on the Application object.
ExcelSheet.Application.Quit 
 ' Release the object variable.
Set ExcelSheet = Nothing 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I get the following error "Run-time error '462'. The remote server machine does not exist or is not available."

Can someone help please?

Many Thanks :D

Regards

Q Auto Merged Post;

OK. I've now turned off the firewall on the PC with win XP. Now i get a different error: "Run time rror '70'. Permission Denied" on the same line of code that gave trouble before.

Any one help!!!

I just followed the instructions to create an addin. Now I can acces all the functions that are in the add in, but only from Excel.

So, let's say I have function1(a) in the addin, and in excel I can do

=function1(a)

How do I use this function in a module in VBA? If I try to use it in a module of the addin, it does work, but addressing it from a new module, I get an error that the function is not defined

Hoping to find an answer here

Greetings

Hello,

In Excel, it is possible to use several colors (or formats) when writing text to a cell.
How would that be done from a macro?

Code:
Usually one would do something like this:

myCell.Value = "myText1 myText2"

and one would get the whole text in one format. 
The format could be changed in VBA too.
But what about mixing format from VBA, as that can be done in the user interface?


I recently posted a topic that required the use of NETWORKDAYS within a UDF.

Although I can use application.worksheetfunction. to use inbuilt functions from VBA, It would not let me use the analysis toolpak function.

I also could not use them using the evaluate method.

In the end, A solution was provided that recreated the functionality of NETWORKDAYS.

Although this solved the problem at that time, I am curious how you can use analysis toolpak functions from VBA.

Can anyone help?

Thanks in advance.

Mark Perkins

The following code produces a permission denied error when trying to copy a
file.

Private Sub ReplaceDep(sCurPath, sTPPath)
Dim oFileSysObj As Object
Set oFileSysObj = CreateObject("Scripting.FileSystemObject")
' Following line gives "Permission denied (Error 70)"
oFileSysObj.CopyFile sCurPath & "Deploy2.bat", sTPPath
End Sub

sCurPath and sTPPath are both valid paths and neither have any write
restrictions. The file to be copied exists, is not protected in any way and
is not open.

I've tried replacing the variable with hard coded paths. Out of desparation,
I've tried adding ", True" at the end of the line to force overwriting, even
though the filename does not exist in the destination folder and True is
default for the argument anyway.

I've never used CopyFile before so I may be missing something really obvious
(though not to me).

Using other code I am able to save an open XL file to the same folder
(sTPPath), so there's no problem writing to the folder with VBA.

Any ideas?

--
Ian
--

We have an Excel workbook with macros to manipulate files on a Windows
server. The Excel workbook is accessed on a user's workstation. Everything
worked fine under Windows Server 2000. We have rebuilt the server with
Windows Server 2003. Now when the macro tries to delete files from the
server, it gets a "permission denied" error. The user has full control
permission in the folders and can manually delete the files. The current
hypothesis is that there is a setting in Server 2003 that is defaulted
differently from Server 2000, but we don't what setting this might be.
The macro uses the Scripting library and loops through the files in a folder
and uses the Delete method. The user has the option of archiving the files,
in which case the files are moved rather than deleted. The Move method works
but the Delete method doesn't.

Is there a way, given a file's path & name, to determine if it is a password
protected Excel workbook?

And, if it is a password protected workbook, can I open it from VBA using
that password, so I can modify it and save it from VBA?

Thanks,
Ken

We have an Excel workbook with macros to manipulate files on a Windows
server. The Excel workbook is accessed on a user's workstation. Everything
worked fine under Windows Server 2000. We have rebuilt the server with
Windows Server 2003. Now when the macro tries to delete files from the
server, it gets a "permission denied" error. The user has full control
permission in the folders and can manually delete the files. The current
hypothesis is that there is a setting in Server 2003 that is defaulted
differently from Server 2000, but we don't what setting this might be.
The macro uses the Scripting library and loops through the files in a folder
and uses the Delete method. The user has the option of archiving the files,
in which case the files are moved rather than deleted. The Move method works
but the Delete method doesn't.

Hi,

I am triying to use real time values from VBA, so I type:

WorksheetFunction.RTD("RTDEngine", "",BLJun - 9, "T") which works perfectly well from excel, but I can't access the value from VBA.

Can you please help me to sort this out?

Many thanks,

Valeria

Hi Guys, i am from brazil, i am creating excel plan, but i would like to put some complex formulas on it to create report using data from another sheets.

Sorry for my bad english. My native language is portuguese.

I translated my plan from portuguese for all forum users understand.

Please download my plan here: http://www.witson.com.br/uploads/englishversion.rar

I will use this plan to control payment Receiving (check and slip).

My objective is Sheet (Resumo) Get automatically data from anothers sheets (Loja 1, Loja 2 and Loja 3).
For example Open Shop 1 (Loja 1 in Portuguese), see dates, month 04 go from Number 1 to 18, now go to Resumo Sheet, you will see month 04 checks copied.

I want that Resumo Sheet get data in this sequence:

Get all lines for month 04 checks from Loja 1 Sheet > Get Names Fields and all lines for month 04 slip from Loja 2 and Loja 3 Sheets > Get Names Fields and all lines for month 05 checks from Loja 1 Sheet > Get Names Fields and all lines for month 05 slip from Loja 2 and Loja 3 Sheets > Repeat until Finish

I use 2 macros on my plan, go to Loja 1 Sheet and type in line 67 any number in credit (it automatically will create new line waiting new data entry..) and i also am trying auto update Resumo Sheets using Macro, go to Resumo Sheet and Press Data Update Button (you will see that automatically it will get all 04 month lines from Loja 1 Sheet, but i am not able to develop something better for now, i just started use VBA 4 days ago and i dont know Programming language.. I dont know if better way to create this list is using macro or another way, please help me, i only will be able to use this plan after help from this forum users..

Only to understand, i already tried post this doubt from more than 6 excel forum from brazil and anybody was able to help me, becuase of that i researched better excel forum and translated all my plan to try get help..

If not understand anything, please reply that i will try clarify..

Thanks for All

Hi,

I am wondering whether it is possible to call puTTy from VBA. (Just FYI puTTy is a terminal emulator used for remote access to computer systems).

I have a spreadsheet with IP addresses, account names, and passwords, and if possible I would like to then start a puTTy session using VBA, and pass the relevant system information to it. This will allow one-click logging on to another system.

I can do the passing of variables no problem - it's the calling of an application from VBA which I'm having trouble with.

Any help would be very much appreciated - there's no hurry though as this is a "like-to-have" part of my VBA, as opposed to a must.

Thanks in advance

Rich

Hi,
I am trying to write to the event log from VBA. I have managed to write to the Event Log(You need to start the NT Applet, Run - EVENTVWR.EXE) using the following article:
http://support.microsoft.com/?kbid=154576......Since we're using VBA, there is no application.logevent, so you need to use the example shown for VB 4. However the log looks like below as I seem to be missing a registry setting.
Do you know what registry setting I need to get this to work?(Clean error/warning message)
I am using VBA to write out the Log (Excel 2001 and VB 6.3)

EVENT VIEWER: APPLICATION LOG
The description for Event ID ( 1001 ) in Source ( Project1 ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: Information from Project1.

Hi alll,

In my workbook I have several columns with headers the code snipet below uses the match command from vba to return the column that it is found in.


	VB:
	
WorksheetFunction.Match(strName, Sheet1.Range("1:1"), 0) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This works fine when Sheet1 is the active sheet but it does not seem to work if it is not.
Has anyone had this problem before?
If so how have you overcome it?
The only other way that I can think of is to create a function in vba to do the same thing.

TIA
James

hi,

We use Advent Axys in our company. I use the following VBA line from Excel to call the scr script:


	VB:
	
 
Private Sub cmdExportfromAxys_Click() 
    Shell ("G:axys3axys32.exe script export.scr -exit") 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But I'm getting the following pop up error in Axys:
'File cenviw.exe not found'

Any suggestion or advice much appreciated. Also the script runs fine in Axys itself

Hi. I am receiving a timeout error when running a SQL query from VBA. Here is the code I am trying to run (I inherited this code and admittedly do not know exactly what each of the commands is doing):


	VB:
	
 
Dim SQLQuery As String 
Dim SQLData As ADODB.Recordset 
Dim FirstDataCell As String 
 
SQLQuery = _ 
"THIS IS THE SQL QUERY I WANT TO RUN" 
 
SQLServerPath = "Provider=SQLOLEDB;" & _ 
"Data Source=MYDATA;" & _ 
"Initial Catalog=MYCATALOG;" & _ 
"Integrated Security=SSPI" 
Set SQLData = New ADODB.Recordset 
SQLData.Open SQLQuery, SQLServerPath, adOpenForwardOnly, adLockReadOnly, adCmdText 
If Not SQLData.EOF Then 
    wks.Range(FirstDataCell).CopyFromRecordset SQLData 
    SQLData.Close 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The error occurs on the SQLData.Open line. The error is "Run-Time Error '-2147217871 (80040e31)': Timeout expired"

The SQL query I am trying to run works fine when run directly from the Query Analyzer. I have gotten short queries to work using this code structure, but not longer ones (the error occurs after about 30 seconds).

After searching Help, I have tried the line:

	VB:
	
Application.ODBCTimeout = 0 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but this did not appear to do anything.

Does anyone have any suggestions?
Thanks!

Greetings,

I have been trying to get this code to work, but to no avail!

I keep getting a permission denied error. Here is where debug freaks out:

	VB:
	
On Error Resume Next 
For lRow = 1 To rRng.Rows.Count 
    With rRng(lRow) 
        NoDupes.Add .Value, CStr(.Value) 
    End With 
Next lRow 
On Error Goto 0 
 'copies each item in NoDupes into the chosen Combox
For Each item In NoDupes 
    cmBox.AddItem item '

Hi,

I am trying to create a .csv file in Excel 2003 and need to Output to the file using an Excel cell range (I have to create a .csv file for every Excel WorkSheet in the current WorkBook).
Where strOutFolder = "C:Test" And ws.Name is the current WorkSheet.


	VB:
	
 FileSystemObject 
Dim intFileNum As Integer 
Dim objNameCSV As Object 
 
.... For Each WorkSheet 
 
intFileNum = FreeFile() 
 
Set fs = CreateObject("Scripting.FileSystemObject") 
Set objNameCSV = fs.CreateTextFile(strOutFolder & ws.Name & ".csv", True) 
 
Open strOutFolder & ws.Name & ".csv" For Output As #intFileNum 
 
....Input using the cell range here 
 
Close #nFileNum 
 
.... Next WorkSheet 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The following error message appears

Run-time error '70':
Permission denied

Why can't I access the .csv file using the Open statement? Is there an easier way around this?

Thanks in advance.

Neal

From vba excel I use a browser dialog box where the user selects a file for the application to read. How can I get the servername of the file the user selected? By default I get the mapped drive C:/K: etc which is not what i need.

thx,
-M

Hi and Many Thanks in Advance!!!

I have on my Worksheet a ScrollBar1 attached, "NOT A USERFORM", just a Scroolbar button.
My ScrollBar comes from "the Toolbox Controls Toolbar".

How van I change this ScrollBar1 properties from VBA?

Say, some thing like:

HTML Code:
Sub Hello()
    With ScrollBar1
        .Min = 1
        .Max = 400
        .SmallChange = 5
        .LargeChange = 40
        .LinkedCell = Range("A2")
    End With
End Sub
by the way:

I do not want to use:
HTML Code:
Sub Hello()
Dim MyShape
    Set MyShape = ActiveSheet.Shapes("Thanks")
    With MyShape
        .Select
        With Selection
            .Value = 0
            .Min = 1
            .Max = 400
            .SmallChange = 5
            .LargeChange = 40
            .LinkedCell = "A2"
        End With
    End With
End Sub
I mean, I do NOT WANT to use "UserForm Controls"
Once more Time: Thanks

We all know how to send e-mail from VBA.

The possibilities
Use Sendmail to e-mail the workbook to someone using the default client

Use Outlook automation to create an e-mail to e-mail someone

Use other client automation to create an e-mail to e-mail someone.

Use ShellExecute to e-mail using the default client and sendkeys to press the send button and close it down.

The problem: Corporate policy is that we must use LotusNotes, Outlook is not available. LotusNotes is a persnickity little program which doesn't like being used as an e-mail client unless it rules the computer. No, my company's IT department hasn't found a way to get it to open and send e-mails reliably without tieing it into the Windows login. The problem arises when I have one e-mail address to put on three different computers with three different logins at once.

So, I need a way to bypass the corporate mandated default e-mail client, and send an e-mail without Outlook.


No luck finding an answer? You could always try Google.