select [C1] from [values$]
It works, but my problem is to write this values.
Why?
Cause i work with my workbook open (not that from connection) and i have something like 55,000 rows to change value. A complex range:
D2,D11:D12,D14:D20,D32,D42,D52,D62,D72,D82,D92,D101:D102,D104:D112...
I have a loop for each cell in this range, but this process is very slow.
Imagine:
For Each rng In rng
rng.Value = objRecordset(0)
objRecordset.MoveNext
Next rng
55,000? So slow...
how to write content of an array and a tab to a file. I have all the file handling working, my questions is how to do the actual write. The items in the array are all text strings but this doesnt work
Write #1 myRng(i,j) & vbTab
with the obvious assumptions. I just want to write the array value and a tab to my tab-delimited file in the next location. tia.
What I am trying to do is create a list of unique values under certain conditions. I will try to explain it as best as I can.Imagine we have four columns:
Column A contains a product, so for example Apples, Bananas, Oranges... Column B contains names, so for example Peter, Carl, Linda... Column C contains stores, so for example Supermarket, Cornershop, ... Column D contains a number, so for example 1, 2, 3...
Now let's say I want to see who bought apples in the supermarket, I would like to query kind of like this:
For all rows where column a = "Apples" and column c = "Supermarket", write me Column b value and expect something like
Peter Linda
if only these two bought apples in the supermarket.
But I need to make sure its only unique people, so if Peter bought apples at the supermarket twice, I do not want to see
Peter Linda Peter
Now the next step would be to see what was the maximum amount of apples that peter has ever bought at the supermarket, so Im thinking of something like:
For all rows where column a = "Apples" and column b = "Peter" and column c = "Supermarket", find me the maximum value that is ever displayed in Column D.
Like I said, I'm totally new to VBA so I am not really sure how to get started with this, but what I do know is that doing this with if statements would take me forever, so I want to give vba a try.
I am new to programming in excel but am looking to make a VBA that allows me to see if a value entered exists in a second spread sheet. If it does then write a comment next to it. If not then write a different comment.
This is what my spread sheet currently looks like:
Sheet1 contains all the important information: A B C D E F
Load PO Delivery Email Item ShipCon
1 3456 6543 Y 5219
5129
2 2345 5432 Y 1234
4321
Sheet2 contains the items that contain a shipping condition. There are two shipping conditions Fragile and non-Fragile. The list is of Fragile items.
Item
5219
1234
I am looking for a way to have it set up that when i enter an item number in sheet in 1 it searches in sheet 2 to see if it is fragile, if it is then returns, in sheet 1 column F next to that specific item 'Fragile'. If it doesn't find it in the list returns 'non-fragile'.
Im using excel 2010 As it's 60 times quicker I was trying to speed up my code and replace all loops by putting the value into an array, and then transfer the array to the worksheet
It seems to be straightforward for math calculations like in this example:
[URL]
But no luck with the one below. I was trying to test it on a simple loop which replaces two types of string into the 3rd one:
Code:
Dim lastrow, lastrow2, i As Long With Worksheets("KPI5") lastrow2 = .Range("N" & Rows.Count).End(xlUp).Row .Range("T7:T" & lastrow2).Value = .Range("F7:F" & lastrow2).Value For i = 8 To lastrow2 If .Range("T" & i).Value = "Modification" Then
i have two workbooks that use a identical worksheet which is called products (this sheet contains rows of over 19000 products ). The problem is i cannot merge the two workbooks together because they are just too big in size.
Also these workbooks have similar userforms and macros that rely on this product databases (worksheets).
My problem is when i add a new product to of one product databases i have to also add it to the second product database again. Is there a way where i can update both worksheets together without opening both workbooks.
I have three userforms that rely on this worksheet from both workbooks:
frmNewProduct - To add a new product to the worksheet.
frmProductUpdate - To edit, replace or delete products in the worksheet.
frmSearch - To search for products inside the worksheet.
I have also got other define names, worksheets and formulas that rely on these product database worksheets.
I'm trying to connect to SAP R/3, and I think I'm not sure which references (Tools --> References) I'm supposed have checked.
All I want to do for now is the following:
Dim sapConn As Object Set sapConn = CreateObject("SAP. Functions")
Unfortunately, I am getting a "Run-time error '-2147024770 (8007007e)': Automation error. The specified module could not be found."
I have read somewhere random on the internet that I should add "SAP:Remote Function Call: COM Support 1.0 Type Library" which links to the SAPGui Provided library librfc32.dll. However, this alone doesn't seem to work...
I was messing around with data connections and importing stuff from a network in real time. Cool stuff. But everytime I open ANY excel worksheet on this computer, it tells me "file cannot be found" and refers to one of the first files I used for this. this happens no matter what workbook I am opening.
I have a series of external database connections that I run in Excel, connecting to a Server based DB and running some SELECT statements. (ingres). We may have up to dozen of client machines accessing the ingres DB at any time. All is good at this point but when excel goes to INSERT some calculated data back to the main database, it appears that my Excel spreadsheet connection is locking the database when it connects, keeping other people from using their client connections.
first question: Does anyone know if the connection excel uses to run sql statements via external database connection will indeed prevent other users from connecting to the main database?
second question: if so, is there a setting or something that can make excel more "passive"?
This file connects to a weather API. The laptop I am using connects through a VPN connection. For some reason whenever I am on the VPN if I try to refresh the information I will get an error and the macro will fault out. Other than that the macro works perfectly fine.
Attached is the file in question : Weather Forecast Spreadsheet Draft V12.xlsm
When debugging the error goes to:
How can I place in an error management instead of the code just getting blocked out.
I have a VBA application that uses data from a sheet in the excel workbook. It accesses this data with an ADO connection. On workbook open I open this connection--I declare it as public variable so I can continue to use it throughout the session. My problem is that the connection really doesn't ever dissappear. I have the problem that once I close the workbook, the project still exists in the VB Editor. How do I get rid of the connection? I am stuck because I declared it as public? I want to keep it open, rather than opening every time I want to use it, because it is faster to keep the connection open.
My code is below.
Option Explicit 'requires variable declaration 'declare global connection Public cn As ADODB.Connection
Sub OpenDBConnection() 'open db connection 'this happens on workbook open If cn Is Nothing Then Set cn = New ADODB.Connection With cn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source=" & WCHSBook.FullName & ";" & _ "Extended Properties=""Excel 8.0;HDR=Yes;""" .Open End With End If End Sub
I'm having problems with date parameter for a web connection. I can provide the parameter and type the parameter via message box ... but when I want to reference a cell the query errors out.
I have a macro which retrieves data from web.It works fine.I just don't know how to assign a cell value in web address.i have attached the code here. i just want to assign activesheet.(range"c2").value for "q?s".
Afternoon All - i have an excel workbook that runs on 20 remote machines that i do not have direct access to - i am placing upgrades on the net but looking at finding a way for excel to detect the update what i need it to do first is check if the machine is connected to the internet.
I am currently querying data from an Oracle database through MS Access and then passing it to Excel for the user to work. Not all users have MS Access so I would like to automate the data pull from Excel
I currently have a workbook that has VBA functions that calculates values by looping through large amounts of data in a few large excel sheets. I want to transfer the data from these sheets into Access and then bring data into the VBA code via an array from the data in Access rather than the array being populated from the data in excel as it currently is. How to create the connection. The access DB will be situated in a public directory on a server. How to create the connection string and then how I call the data from Access? I have just downloaded Office 2013.
I have the following code in Excel which stops the connection to the internet
'code below
Option Explicit Private Const FLAG_ICC_FORCE_CONNECTION = &H1 Private Declare Function InternetCheckConnection Lib "wininet.dll" Alias "InternetCheckConnectionA" (ByVal lpszUrl As String, ByVal dwFlags As Long, ByVal dwReserved As Long) As Long
Private Const INTERNET_AUTODIAL_FORCE_ONLINE = 0 Private Const INTERNET_AUTODIAL_FORCE_UNATTENDED = 0 Private Declare Function InternetAutodial Lib "wininet.dll" (ByVal dwFlags As Long, ByVal dwReserved As Long) As Long Private Declare Function InternetAutodialHangup Lib "wininet.dll" (ByVal dwReserved As Long) As Long
My workbook has 9 queries, all needing the same string. I would like to have a msgbox pop up, let them enter the directory name, and have that change all the odbc conneciton strings automatically. Can anyone point me in the right direction?
The message box would be a nice touch, but is not necessary, I can change it in the VBA editor if needed.
Currently, they connect using a Visual FoxPro ODBC driver. We setup the connection when we made the queries. Now that they are in on the worksheets, I want to be able to edit them with VBA instead of using the script editor (which is very slow).
I've created a connection which draws information from an access table to excel. The issue is that any numbers (they are ratings from 1-10) that come from access are shown in excel 1 number higher than the correct data that is in access (i.e 10 is shown as 11, 9 as 10, etc). I've looked all over the forum as well as google and can't find any information on how to correct this.
I'm trying to create a dropdown list on my "job master" sheet with reference to cells in an ODBC connection where a position "description" will give me a list of names. Then I want to be able to select another list of names individually with reference to the name I Just selected.
Staff Description Foreman
[Code].....
*I was able to do this using lists and indirect list (Personnel Sheet) but it doesn't work with my ODBC connection. I attached the excel file I am using.
I have an excel document saved in a SharePoint document library. I then have another excel document where I created a connection back to the document being saved on SP (went to data connections->add->browse for more->enter the SP URL & found the document) and then opened that connection up as its own worksheet (existing connections->chose my connection). In the original worksheet (saved on SP), there are about 20 columns that are formatted as numbers. However, when I open the connection up in the new document, half of these columns are still number formatted, but the rest are being displayed as text EVEN THOUGH they are technically formatted as numbers (I right click->format cells & they are formatted as numbers). I can click into a cell and press enter and it will "come up to speed" and enter into number formatting, but as soon as I refresh my connection back to the original document, they go back to their text formatting despite the fact that the connection they are drawing from has them saved as numbers! Very frustrating. Its not a SP issue because I recreated the scenario by saving the original document to my computer and it did the same thing. I even went through one column in the original document and made sure there were no spaces saved to trigger the column to go to text- nothing.
I have 2 excel files. One excel file (File A) is a basically a source file on a shared network that users input data into. The second excel file (File B) I created in hopes of basically using to analyze the data in File A. I used a data connection to access the data on File A while on File B. The issue I'm facing now is that when I refresh the connection, it locks File A from being opened until I close File B. Currently, I have the data connection to refresh only manually, so I'll open File B, refresh it, save, close, and then re-open without refreshing, so that way File A only stays locked for that short amount of time. Is there any way to make it such that when I refresh the connection, it doesn't lock File A?
I am connecting Excel to a database and there is a field called Data Created which is timestamp. Data comes out nicely in a Pivot Table
1. I would like to be able to group by month but the grouping fiction is grayed out
2. How can we convert the timestamp to only date format. I am able to select only Date for that column but it seems that it keeps the time, even though it only shows the date in the pivot table the bar above shows date and time.
I am not sure how to setup the initial connection to the datbase from excel and was wondering what would be the best way to go about things. Is it possible to run the query stored in the database and pass it a value or would I be better off just coding it into a sqlstring within the macro.
Here is the query that is in the database...when run it asks for the "Status?" variable.
SELECT ordernumber, mobilenumber FROM bookings WHERE status = [status?];
So yes, really simple query but yeah just want to know how to pass it that variable from excel or can I just set it up in the actual macro to maybe read the status from a cell?
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 have an Excel workbook that has a data connection to a Sharepoint List. I have 4 columns showing the quaterly status of a project. I want to apply conditional formatting based on the value in the column. I have 5 possible choices so I have to use VBA code to accomplish this because of the limit of 3 option in Excel. I am using this
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Range("X:AA")) If rng Is Nothing Then Exit Sub Else Dim cl As Range For Each cl In rng Select Case cl.Text Case "1" cl.Interior.ColorIndex = 4 Case "2" cl.Interior.ColorIndex = 6 Case "3" cl.Interior.ColorIndex = 3 Case "4"...............................
i currently have an excel based application with VBA code. I'm trying to add some controls around the app by prompting user for login and password credentials prior to ODBC connection at runtime. So when application is launched, prompt appears, user enters login and password, if correct, it goes on to verify user has ODBC connection at runtime.
