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.
View 6 RepliesI 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
Sub Stop_BroadBand()
If InternetAutodialHangup(0) Then
End If
End Sub
'code end
to retrieve the data from MS access table in to the DB Grid at user form through MS SQL Query.
I am trying to use ADO for the same.
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 need to send a file to hundreds of users so that I can tell who has a particular add-in installed. This file will then be saved to a particular directory. The problem I'm having is controlling the error that occurs when the add-in is NOT installed. The below approach (using On Error Goto) clearly isn't the way to go. I need something like
If(Iserror(AddIns("AmartaSpecs") Then Cells(2,3)="No"
The code I have so far is:
Sub Amarta_Specs()
Dim a
'ActiveSheet.Shapes("Button 1").Delete
Cells(1, 1) = "Username"
Cells(1, 2) = "Computer Name"
Cells(1, 3) = "Add-in installed?"
Cells(2, 1) = Environ("Username")
Cells(2, 2) = Environ("Computername")
On Error GoTo Error
Set a = AddIns("AmartaSpecs")
Cells(2, 3) = "Yes"
Error:
Cells(2, 3) = "No"
ChDir "E:FinanceSQL Add-in test"
ActiveWorkbook.SaveAs Filename:="E:FinanceSQL Add-in test" & Environ("Username") & ".xls"
End Sub
I am trying to create a code in the Worksheet_change event. I have hundreds of rows of data in a sheet. Each row of data is one unique project. What I am trying to do is figure out if any cell in a specific row has changed, and place a date at the end of that row idicating the date of the change.
View 9 Replies View RelatedI 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.
When I click data --> connections its empty.
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 have a ADO Connection. My query is like this:
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...
I saw something about QueryTable,
Sub WebQuery()
Dim strSearch As String
strSearch = "abc"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.xyz.com/&=" & strSearch & "", _
Destination:= Range("A1"))
.Name = "search?hl=en&ie=UTF-8&oe=UTF-8&q=" & strSearch & ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
End Sub...
I'm trying to run som e query using VBA. How can I open a database connection to mySQL?
View 2 Replies View RelatedHow to detect msgbox pup up event.
Does any function like msgbox.activate=true or somrthing can used.
I want to avoid msgbox show up and close msgbox automaticly.
I know
application.displayalters=False
But it's seem not to apply to msgbox close.
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.
View 3 Replies View RelatedI 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".
View 1 Replies View RelatedI 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
Database information:
The DNS is: a150
The Tables are:
MFE.Items
MFE.Vendors
Sample SQL string would be:
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.
View 9 Replies View RelatedMy 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.
View 9 Replies View Relatedhow can I login to a website using vba. I googled many examples but still failed.
View 9 Replies View RelatedI am trying to edit the Copy menus so that they only paste values. I have the following code in the workbook object:
Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
Application. CommandBars("Edit").Controls.Item("Paste").OnAction = "New_paste"
Application.CommandBars(" Cell").Controls.Item("Paste").OnAction = "New_paste"
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
Application.CommandBars(1).Reset
End Sub
and this code in a module:.........
The module code is however bringing up an error message 'User-defined not defined' and I am not sure why.
I would like to run a spreadsheet on one machine and watch it on another machine across the internet. either excel to excel or excel to web page the data is live and changing so i would rather not have a web page i have to refresh all the time
i thought excel to excel would be the cleanest way. how can i achieve this
I searched this site and found the code below to check if a worksheet to be updated is already open. It works fine (gets the desired message) if the workbook is open, but if not open I get "Run-time Error 9, Subscript out of range". I've played with it for a couple hours,
VB:
Dim wBook As Workbook
'Check that Summary worksheet is available for update.
Set wBook = Workbooks("CommittedSummary.xls")
If wBook Is Nothing Then 'Not open
'Do nothing
Else 'It is open
i = MsgBox("The 'CommittedSummary.xls' workbook is already in use and
cannot be updated. Please try again when the workbook Is available", _
vbCritical, "Committed Request Edit")
Goto EndUpdate
End If
I have a column with employee id's. Sometimes two or more employee ID's are double. I need to dedect if there are ID numbers that appears more then ones. If I found such a number I can copy that.
Has someone an idea or is there a build in function to handle this?
How can I detect on an excel workbook which cell have links(formula =) to
other workbooks without have to put my cursor on top of each linked cell.
I have had a hard time finding this cells. I can see the links by choosing Edit
then links, and excel will display list of links, but it does not specify
which cells contain those links
I am doing a bit of data spring cleaning... I need some VBA that will look at column FT for duplicates. I want it then to keep only the most recent one, so it would delete the others rows completely. Column B contains a date and time. When removing the items it should make a copy of them to the sheet Delete Log.
View 1 Replies View RelatedI have a workbook with a few worksheets. In sheet1 I have a few ranges which are linked to different parts in the other excel sheets.
Is there anyway to check if a range is being used as a link in another sheet.
Eg.
In sheet1 , A1 i have a value Apple.
In sheet2, A1 I have linked it to sheet1 A1.
is it possible to detect the link in sheet2 A1 from Sheet1 A1? I tried using Trace dependents but all it shows me is the link symbol? An arrow with a small excel sheet.
Is there anyway to detect if the user has just printed. I have code which transfer data from the sheet to an access file when the user prints using the button I placed on the sheet, unfortunately some users are using the print button on the toolbar.
View 9 Replies View Related