How to unlock the file being connected to, after the data connection is not needed anymore using Excel VBA?
My goal is to actually update the Excel file using a data connection, then cut the file off after update in case there is a need to update the file being connected to, then reactivating the data connection before closing the file for the next session.
A suggestion was:
Private Sub DisableConnections() Dim conn As Object For Each conn In ActiveWorkbook.Connections conn.ODBCConnection.EnableRefresh = False Next End Sub
But still locked the file that was being connected to by my Excel file.
See also questions close to this topic
How to fix Excel import and compare error?
I'm comparing two Excel files and want to write matches to new file using some filters.
I'm comparing two Excel files and if there is matching in Make, Model, Modification, Horse Power and also from "WheelSizeFullDB_new" year is in range of year start and year stop of "sql-autobaza", then I want to create a new file and add to it all column of "sql-autobaza" and also 2 last column from "WheelSizeFullDB_new" Tire Size Front and Tire Size Back
sql_base : drive.google.com/open?id=1Dk_1q9n5RgKFRawT7qBwyMY4ldGUL0fb
sab_base : drive.google.com/file/d/1AewxBR9p0Tgxi2i-iXS_9RDCd90hsA4G
import pandas as pd import re sab_base = pd.read_excel('C:\\Users\\x\\Desktop\\Reziko\\Programming\\Visual Studio 2019\\WheelSizeFullDB_new.xlsx') sql_base = pd.read_excel('C:\\Users\\x\\Desktop\\Reziko\\Programming\\Visual Studio 2019\\sql-autobaza.xlsx') sqlbase = sql_base.loc[ (sql_base['Make'].str.contains('%s[a-z]*'%sab_base['Make'], flags=re.I, regex=True)) & (sql_base['Model'].str.contains('%s[a-z]*'%sab_base['Model'], flags=re.I, regex=True)) & (sql_base['Modification'].str.contains('%s[a-z]*'%sab_base['Modification'], flags=re.I, regex=True)) & (sql_base['Horse Power'].str.contains('%s[a-z]*'%sab_base['Horse Power'], flags=re.I, regex=True)) & (sql_base['Year Start'] < sab_base['Year']) & (sql_base['Year Stop'] > sab_base['Year']) ] print(sqlbase) sqlbase.to_excel('sab_base_update.xlsx', index=False)
I expect to create new file and add to it all column of "sql-autobaza" and also two last columns from "WheelSizeFullDB_new" Tire Size Front and Tire Size Back but my code does not work
What is the Excel Date Format to retain dates in this specific format?
I am exporting the output of a
Excel 365and one of the columns contain
SQlquery output displays those values as
2014-05-08 12:14:50.000. I want to format this specific column in
Excelso that it displays the values in its original format.
How can I do this in
How to mimic Excel's LOGEST function in Python
I'm interesting in mimic Excel's LOGEST function in Python but have no idea where to start.
TRIM & Remove Duplicate and TEXTJOIN a variable range using VBA
I would like a range most varying say A1:A20 pulled from an application with space in beginning or in end so it need to be TRIM and then Remove Duplicates, remove blank Cells and they need to be TEXTJOIN(",",TRUE,A1:A20) Range is variable. So that I can copy paste this to QMF for running query. Appreciate your help in creating this VBA so that I can create a button in the ribbon and can use in any excelsheet.
macro to transpose a table into column
i have numbers are arrange in table like in first row 1 to 10 then in next row 11 to 20 then 21 to 30 and so on i want that each row should transpose into single column like in any column 1 to 10 then below 10, 11 to 20, then 21 to 30 and so on
If function evaluation
My code should get value from user than paste data according the given value. But my code would not consider user input and just paste all the data from data sheet to myrpt sheet in same workbook
Sub myprintablereprote() Dim dsheet As Worksheet Dim rptsheet As Worksheet Set dsheet = ThisWorkbook.Sheets("data") Set rptsheet = ThisWorkbook.Sheets("myrpt") 'Row Num in other sheet y = 2 'get last row from dsheet(data sheet) lastrow = dsheet.Cells(Rows.Count, 1).End(xlUp).Row myib1 = InputBox("how much many ", " How much ") + 0 For x = 2 To lastrow If dsheet.Cells(x, 4) = myibl Then ' Moving datat from dsheet to rpt sheet rptsheet.Cells(y, 1) = dsheet.Cells(x, 1) 'name rptsheet.Cells(y, 2) = dsheet.Cells(x, 4) 'sales amount 'Add data to new row on rptsheet y = y + 1 End If Next x End Sub
VBA loop to next url after 15-20 url it start overriding data in same cell. Why it start putting data on wrong cell after some url?
I am trying to get data from url & loop to next and put in next empty column. all working fine but after some URLs start overriding data in seme cell.
Why VBA start overriding after 15-20 urls.
Accessing an image that's inside of an Excel Table via VBA
I am designing a VBA Form in Excel. The Workbook has a table called "images", and inside there I am dropping some images from my local hard drive.
These Workbook & UserForm are to be shared with my colleagues. They might not have these images in their harddrive, but they will have them inside of the Excel table.
I am looking for a way to load an image that's inside of a table inside of an "Image" VBA form control.
In Google all I find is how to load an image from my hard drive (i.e. using an absolute path like "C:/my_images/car.png"). What I can't find is how to load an image that's within a table, i.e. already bundled within the Workbook.
Excel VBA - Replace chart marker with picture
I would like to replace the markers of a line chart with a picture in VBA.
This is equivalent to:
I copied/pasted this picture in a sheet of the workbook and called it
With .ChartObjects("graph").Chart .SeriesCollection(1).MarkerStyle = xlMarkerStylePicture ... End With
What should I use to replace
...and point to my image?