Cannot Locate Internet Server Or Proxy: Web Query
Oct 6, 2006
Currently my company is importing the data to excel spread sheet from the following website http://www.pjm.com/pub/account/lmpgen/lmppost.html and the data is displayed to the price board. It runs 24/7 and it refreshes data every minute.
It was set up thro’ the web query, and there is no macro involved in importing.
Whenever there is a problem with website, it gives the following error “Unable to open http://www.pjm.com/pub/account/lmpgen/lmppost.html. Cannot locate the Internet server or proxy server.’ Until someone clicks OK to the error message, the web query stops importing and the price board gets freezes. I tried with the following macro to avoid the error messages being poped up.
Sub Auto_Open()
Application.DisplayAlerts = False
End Sub
View 2 Replies
ADVERTISEMENT
Nov 26, 2012
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
View 3 Replies
View Related
Mar 29, 2007
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this
WEB
1
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12
Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
View 2 Replies
View Related
Sep 6, 2008
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!
View 9 Replies
View Related
Nov 5, 2008
I have a spreadsheet which I used in a previous job, which was set up by our old IT Department. Basically, it allows me to save information onto it, which I can then retrieve, (in any conditions/search fields i.e. in a date range etc) but the problem is; when I try to use it on my laptop, I look at the editor used for macros, I can see that the spreadsheet is linked to the servers of my old company. Therefore, I cannot use it myself
I do not know a lot about macros etc. but I can send a copy of the spreadsheet to anybody who is willing to help!
I think it is just a case of changing the macros/strings of where to draw and save information to/from, but I don;t know.
View 10 Replies
View Related
Nov 25, 2008
This code should retrieve data from sql server to excel sheet
filtering according to the "where" statement
its a modified recorded macro.
View 7 Replies
View Related
May 2, 2013
I am looking to further improve this code by adding the functionality of logging to a secure HTTPS server using username and password.
Code:
Sub TestFileExistsandDownload()
'This code will test a web address to see if a file exists
'If the file exists, it will download the file.
' It's a mixture of code retreived from 2 sites:
' [URL]...
' [URL]....
[Code]...
View 1 Replies
View Related
Jul 31, 2006
I'm trying to open a file from server.After doing all the changes I have to save the file back to the server in the same name. It is asking for save as option.it shouldn't. I want to save the file in the same path and in same name without asking the option of "Save as". I hv full permission for editing the file
View 4 Replies
View Related
Jun 13, 2008
I try to run a macro on my workbook everytime I open it by getting the data from the ftp file. This will be the service number for my customer. Both the excel and the data file are located in the ftp, so that I can retrieve the file wherever I go. Basically I got some code from the internet which work fine on the local network, but once it come to ftp server I am stuck there.
Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "Ftp://mycompany.com"
Const sDEFAULT_FNAME As String = "Service.txt"
Dim nFileNumber As Long
nFileNumber = FreeFile
If sFileName = "" Then sFileName = sDEFAULT_FNAME
If InStr(sFileName, Application.PathSeparator) = 0 Then _
sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName
If nSeqNumber = -1& Then
If Dir(sFileName) <> "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber..................
View 2 Replies
View Related
Apr 29, 2014
We have a number of internal office servers (Active Directory - Exchange - SharePoint "free" etc etc usual stuff) and I need to create an uptime/downtime calculator in excel with the facility to record the information on a daily, monthly and yearly bases but not sure how to go about this.
I would like a way to record on a daily bases what the uptime/downtime has been for each server and then to record the same for that month in a percentage.
For example in a 24hr period for each month of the year:
For that day Active Directory Server was UP for 24hrs. (100% uptime) - (0% downtime)
X by the number of days in the month i.e.
Over the month Active Directory has been UP for. (XX% uptime) - (XX% downtime)
Exchange Server was DOWN for 1hrs. (95.833% uptime) - (x% downtime)
X by the number of days in the month i.e.
Over the month Exchange has been UP for. (XX% uptime) - (XX% downtime)
SharePoint Server was DOWN for 1hrs. (95.833% uptime) - (x% downtime)
X by the number of days in the month i.e.
Over the month SharePoint has been UP for. (XX% uptime) - (XX% downtime)
View 5 Replies
View Related
Aug 22, 2013
I have a database in sql server i want to make a excel form where in a user will enter the data and print the excel file
E.g.
S.NoDESCRIPTIONPART NO
I want a user to write the item description and data should come directly from database .
View 4 Replies
View Related
Jul 29, 2009
I need to run multiple queries and insert their results into Excel cells. Each query will return only one result (a number, or a null). What is the best way to accomplish this?
Below is an example of the code I'm currently using. It does return data to the cell specified, but I'm guessing there's an easier way to populate multiple cells with the results of multiple queries.
View 14 Replies
View Related
May 28, 2012
I am trying to get some data on a excel. This data is available on a unix OS solaris server.
The data is available in tabular format on the txt file in the server and i need to populate this data on my excel sheet.
How to connect to unix from excel and how to get the data.
View 1 Replies
View Related
Oct 30, 2012
I have code which is convert some range of excel to Png and store to my local drive. This code is working fine.
I have created web site on my intranet and the data store on server, now i use this code and pickup image from my local storage i.e. from "C" Drive and paste in to service folder, bcoz i uploaded report as a Image and it timely refreshed and changed
Is there any way so this code directly store to service folder, if i can use IP address or HTTP path
I had tried with HTTP path but it is not working.
My server path is [URL] .......
OR
Is there any way when local folder got image than automatically server folder also get same image...
Sub bah()
''' Set Range you want to export to file
Dim rgExp As Range: Set rgExp = Range("B2:C6")
''' Copy range as picture onto Clipboard
rgExp.CopyPicture Appearance:=xlScreen, format:=xlBitmap
''' Create an empty chart with exact size of range copied
[Code] .........
View 9 Replies
View Related
Jan 30, 2013
How to pull data with a VLOOKUP to a file located on a Server.
My big problem is not understanding the syntax of the Server location. I've provided it below (obviously with some character changes).
I have a file located on my Hard Drive. I need to perform a VLOOKUP from A2 on this spreadsheet. The File on the Server is called "LookupTest.xls". The Range of Data is from Sheet 1 and is from A2:C4 and I need to pull the data from column C depending on what is selected from A2 from the file on my HD. I understand how to use VLOOKUP, just not when I'm pointing to another file on a Server.
Server mapping from Windows Explorer:
rbbabc$ on 'RP17409 - ABC Database (AHSSRVVN678.tge.com)'
Again, that server mapping is fake, as I changed it, but this is how it looks on Windows Explorer.
I'm trying to keep a master file of Data on a spreadsheet found on a server so that one master file can be updated, and several other tools pull from that Master File on VLOOKUP, Validation Lists, etc.
View 4 Replies
View Related
Feb 13, 2013
I'm creating my first stored procedure ever and it looks relatively good so far. The problem is with passing the parameters, more exactly:
Arguments are of wrong type, are out of acceptable range, or are in confilict with one another
The essential part of the stored procedure looks like:
Code:
CREATE PROC GetUserAuthForApp
@User varchar(7),
@application int
AS
while the essential code calling it from VBA looks like:
Code:
Dim strConn As String 'Connection string to SQL Server
Dim strSQLtoExecute As String 'SQL query string to execute
Dim oConn As ADODB.Connection 'Object for connecting
Dim rs As ADODB.Recordset 'Object for recordset
Dim cmd As ADODB.Command
Dim prmUser As ADODB.Parameter
Dim prmApplication As ADODB.Parameter
Dim stProcName As String 'Stored Procedure name
[code]....
and in that rs.Open comes that error.
What have I done wrong, how do I fix it?
View 9 Replies
View Related
Feb 3, 2009
I would like a windows folder to be created on a server from cell Cbut to be filtered by the Data in B.
dwights folder on teh server to create '124 elm street' from the in C1
james folder to the server to create the folder '15 killfops' street'from C5
\server empdwight
\server empjames .....
View 9 Replies
View Related
Sep 17, 2009
Our small company only has 5 computers in it with mine acting as the "server". I want to put a macro on our customer order forms that all computers can access on the server that will save the file as 3 or 4 cell names serparated by commas and save it in a specific location on the server.
I got the macro to work perfectly on my computer but when I try to type in the appropriate path to the server the file gets saved in their My Documents folder 99% of the time. I did get it to work twice but it seembed to be completely random as it didn't work any of the times before or after.
Here's what I have so far: ...
View 9 Replies
View Related
Sep 24, 2006
to copy files on computer which runs excell.
But is there a way to copy files from/to servers?
View 3 Replies
View Related
Dec 8, 2006
I found the below code in one of the posts and it was working fine uptill few days back. now, i get the error Run Time error '-2147220975(80040211) The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available
Sub CDO_Send_Workbook()
Dim iMsg As Object
Dim iConf As Object
Dim wb As Workbook
Dim WBname As String
Application. ScreenUpdating = False
Set wb = ActiveWorkbook
WBname = wb. Name & " " & Format(Now, "dd-mm-yy h-mm-ss") & ".xls"
wb.SaveCopyAs "C:/" & WBname
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1 ' CDO Source Defaults....................
View 2 Replies
View Related
Dec 12, 2006
I have a problem that I have several workbooks that are getting more widely used by a number of users and I'm starting to encounter problems. The workbooks open files from particlaur locations to retrieve information but I am finding that not everyone has the same drive letter for the same server or shared drive.
For example Server607 could be drive J: for me but drive N: for people in another department. Is there a way to refer to the server location in VBA when opening files rather than the drive path?
View 2 Replies
View Related
Jan 30, 2007
i have a file on network with below code following code give my computer date and time when i click the button but i want Server date and time or any website date and time.
we are useing windows 2000 Professional system for server and i know my server path .
Private Sub CommandButton1_Click()
Sheet1. Range("c2") = Date
Sheet1.Range("c3") = Now
'above code give my computer date and time
'but i want server or internet website time
' i know my server path and we are useing windows 2000 system on server
End Sub
above thing is it possible in excel?
my server time is different from my computer.
View 9 Replies
View Related
Apr 30, 2008
I've created some code to automate file downloads from a server using WinSCP. The code creates a .bat file and a .dat file containing a script to transfer files using WinSCP.
When the VBA code runs the script using Shell, the terminal window opens and closes in a flash and the download does not execute. However, if I execute the .bat file outside of VBA the whole download process works great!
Sub GetFilesFromServer()
Call GetLogin
Dim fs As Object
Dim a As Object
Dim retVal As Long
Dim col As Long
Dim row As Long
Dim PBSPath As String
Dim reportName As String
Dim newName As String
Dim targetDate As Long
Dim reportDate As Long
Dim targetDir As String
View 5 Replies
View Related
Jul 8, 2008
I'm trying to query a query in Access 2003, from Excel 2003.
The query in Access looks like:
AccessQuery: [SELECT VBAFunction(field1) FROM Table]
The query in Excel looks like:
ExcelQuery: [SELECT * FROM AccessQuery]
I use the following VBA code in Excel to excecute the query:
With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _
Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery)
.RowNumbers = True
.Refresh BackgroundQuery:=False
iResultRowCount = .ResultRange.Rows.Count
End With
When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.
View 9 Replies
View Related
Apr 25, 2006
I created an Ms Excel Database Query to bring in data from MS Access. (versions 2002 of MS Excel and Ms Access). The query works fine initially. I can right click, choose Edit Query and change my criteria. Results are returned almost instantly.
My problem is that, once I save the workbook, or autosave happens, I get an error when I right-click to Edit Query: This query cannot be edited by the Query Wizard..
View 2 Replies
View Related
Dec 11, 2012
A user has an excel document and there are 5 specific cells they need to populate. The data is in one of our SQL databases. Is it possible to create a new copy of that excel doc with those fields populated for each record? There are around 2000 records they don't want to manually populate each one.
View 1 Replies
View Related
Jul 18, 2014
Any code that will grab the names of all files from all directories on an FTP server? I have the path & credentials to get to them, but I need a process to drop their names into a spreadsheet to compare against a list of files loaded onto a SQL database.
The directory structure on the server is subject to change, as are the number of files in each repository.
View 1 Replies
View Related
Feb 11, 2009
I have CDO set up on in an excel workbook contained on our network. We have been using the following code for well over a year. It works flawlessly on 12 of our 13 computers. I can send mail through Excel using the following code on my computer. But not on a particular computer. The user at that particular computer can't send mail through the CDO code from her computer but can from other computers.
This problem is definitely isolated to a computer. I had tech support un-install office, use a cleaning tool, then re-install office. The problem persists. Is there a particular setting on this computer that needs to be set that I am unaware of?
Here is the code...
View 3 Replies
View Related
Feb 17, 2009
I have a macros in my spreadsheet when I open it a number in the macros goes up 1 each time the worksheet is opened. I want to put this file on a server that can be accessed by various people. The problem I am getting is every time someone opens it from a different location with a different PC the number starts at 1 again. I want to keep the number going up 1 each time no matter who opens it.
View 4 Replies
View Related
Dec 6, 2011
I've come across an excel spreadsheet that has a pivot table which displays data from a sql server connection query. This worked fine before but recently the database it connects to was moved.
The connection string was updated to reflect this.
The main difference in the connection apart from a new server name, is that where before we were using a Trusted Connection, now we're using a SQL login.
Now only some of the data is shown.
The query still works - if I go into Edit Query (Data>Connections>Properties) the query runs fine. It just doesn't display properly anymore
I think this is something to do with the pivot table rather than any connection properties.
View 2 Replies
View Related
