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
I need to call a Stored Procedure in a SQL Server from VB, but do not have the minimun Idea about doing it....Could you help me ?...I made a lot of attemps copiying code from other treads and scratch it, but so far results = none.
Server name: VAIO/SERVIDOR DataBase: CEDRO I´m using Excel XP.
I am using the following code to run a Stored Procedure Query. It works perfect bringing back 4 columns of data. But is it possible to make it so that it only brings back 2 columns of data. I need the 2nd and 4th, which are named "DateOf" and "Gen", respectively. Someway to include a select command?
I posted here, without luck: http://www.mrexcel.com/forum/showthread.php?t=367809
I'm using the below code to add data to an access database and it's working fine, but I'm trying to figure out what code I would need to add a completely new field to the access database?
So maybe before the export, once connected to the DB, add the new field(s) to the table and then add export the data. I'd have to rewrite export bit to allow for any new fields that i've added, but I can do that.
It's just the actually command I need add the new field to the table. Sure it's just a simple couple of lines, but trying to find something that makes sense !!
I am wanting to find a row in a SQL database using VBA in excel. I know you can use find to find the row, but then how do you replace data on that row with the row I have in my spreadsheet?
I currently have:
Code: If lastrow 0 Then For i = 3 To lastrow recset.Find "Col2 = " & Sheets("Changes").Cells(2, i) 'find the value in B from B3 onwards (i) ' Now its found the row in the SQL, replace with values from that excel row (i) Next i End If
I have some code that will Kill a DB if it already exists, but I want to check if it exists and warn the user before this happens.
I am not that familiar with ADO, so I was fumbling through the Help topics trying to learn about ADO type names, etc. b/c i thought I could use something like:
If TypeName(MyDB) = "ADODB" Then . . .
but even if that ran, the argument in parentheses would be a string and not the actual DB object, so I am at a loss.
With ADO (ADODB.Connection), is there a way to preserve the cell formatting on the worksheet that the recordset data is copied to? Right now, if I format a Cell's font and font-size, then run the Macro to refresh the data, the formatting is gone and it's back to default formatting.
I am simply trying to have some code that would allow me to run relatively simple SQL Queries on Excel Data. This data however will be in a table format within Excel and I will only be needing to query on table at a time; meaning that I don't need relational database features or SQL Joins at all. At most, the SQL Statements will involve WHERE and SORT statements. So, my method is relatively simple:
1. Select an Excel Range (The table being Queried) 2. Provide an SQL Statement 3. Provide an Output range to print the results
After my recent research, I concluded that ODBC/ADODB was the best route. I am very new to ODBC Connections though. Basically, I just set up an ODBC Connection and DataSource using the Control Panel/Administrative Tools. After that, following some templates of others I constructed the following Sub getData(SQL As String, outRange As Range) Dim conn As Variant Dim rs As Variant Dim cs As String Dim outCell As Range Set outCell = outRange.Range("a1") Dim row As Integer Dim col As Integer
Set conn = CreateObject("adodb.connection") Set rs = CreateObject("adodb.recordset")
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.
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]....
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
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..................
I have an Excel Sheet which I use as Database. The database has 11 columns and I insert data with the following function:
Code: Sub testInsert() Dim adoCommand As New ADODB.Command Dim sQuery As String Dim i As Integer
Dim strTest As String
strTest = "test"
[Code] .......
Now I want to retrieve this data. i.e. I want all F1 where F2 and F3 are 0 AND I want them ordered descending. I'm trying to achieve this with:
Code: Sub testSelect() Dim adoCommand As New ADODB.Command Dim sQuery As String Dim mrs As New ADODB.Recordset Dim strTest As String
strTest = "test"
[Code] ....
The result I am getting looks like this: 9 8 7 6 5 4 3 2 15 14 13 12 11 10 1
I assume, that the data is interpreted as String instead of an integer. But I explicitely stated the data as Integer when storing the data into the DB.
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)
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.
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
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.
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.
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....................
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?
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
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
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.
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.