VBA Excel update file without opening it
Rochelle13
-
danielc0 Posted messages 2192 Registration date Status Member Last intervention -
danielc0 Posted messages 2192 Registration date Status Member Last intervention -
Hello,
I currently have 2 Excel files with identical column headers. The first is an extraction from an application other than Excel, and the second is a file with a Classic VBA macro.
My request: Is there a trick to copy the rows from the first file to the second without opening the files, and do this every morning at the same time?
Thank you in advance for the attention you will give to my question!
I currently have 2 Excel files with identical column headers. The first is an extraction from an application other than Excel, and the second is a file with a Classic VBA macro.
My request: Is there a trick to copy the rows from the first file to the second without opening the files, and do this every morning at the same time?
Thank you in advance for the attention you will give to my question!
8 answers
-
Hello,
You can use an ADO connection to read from or write to a closed Excel file.
Sub ADOcnx_XL_Close() 'Check the box "Microsoft ActiveX Data Objects x.x Library". 'in Tools > References... Dim Cnx As ADODB.Connection Dim File As String 'Defines the closed workbook serving as the database (provide the full path) File = "C:\myWorkbook.xls" Set Cnx = New ADODB.Connection '--- Connection --- With Cnx .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source=" & File & _ ";Extended Properties=Excel 8.0;" .Open End With 'Extended Properties=Excel 8.0 is used for Excel versions 97, 2000, and 2002. ' '... the query ... ' '--- Close connection --- Cnx.Close Set Cn = Nothing End Sub
;o)
--
“What is well conceived is clearly expressed, And the words to say it come easily.”
Nicolas Boileau-
Hello,
I can't seem to use ADO references; they might not be enabled on my workstation, and I can't find out how to do it.
It says in the project references to check the ADO reference box, but there's no way to find it.
Thank you for your help.
Hello,
there will always be an open file: the one where the command macro is... so, I would suggest performing a simple read from the "source" file to the second one where there is already some VBA
you can find attached examples of office links (excel-access-word) using ADO technology with read-write in a closed excel... command file: home_ado.xls
http://www.cijoint.fr/cjlink.php?file=cj200902/cijpjc7JtG.zip
(since quite a few things have improved)
however, regarding this demo, we can avoid having to check the ADO reference in the references
which avoids the need to configure each computer.
dim source as object dim requete as object 'path and file are global variables coming from elsewhere Set Source = CreateObject("ADODB.Connection") Source.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Path & "\" & File & ";Extended Properties=""Excel 8.0;HDR=No;"";" Set Requete = CreateObject("ADODB.Recordset") .... requete.close source.close set requete=nothing set source=nothing
HDR=NO is used when there are no labels (or field names), if this is the case, you must specify the sheet-tab and the range of cells for example:Set requete = source.Execute("SELECT * FROM `" & sheet & "$" & zone & "` WHERE '" & field & "' <>"""";")
if not specified ADO will look for labels (HDR=Header)
Regardless of the WIN-office version used, keep extended properties at 8.0; ADO will understand it in later versions...
I would like to mention
http://frederic.sigonneau.free.fr/
a must-visit site for any excel addict!
Now, if you don’t have a lot of data-cells to transfer you can use the macro called Walkenbach without calling ADO
another example (the file name was in a combobox):
' model for writing param='C:\my documents\riri\[Workbook1.xls]Workbook1Sheet3'!L1C1 param = "'" & path & "\[" & Me.CbxWorkbook.Value & "]" & sheet & "'!R1C1" ' macro called John Walkenbach field = ExecuteExcel4Macro(param)
but you have to either loop or write as many lines as there are cells
--
The forum is based on knowledge sharing: I do not answer technical questions by private message.
Kind regards, MichelTo modify a record in a closed workbook, I found this
on DVP.com AUTHOR: Silkyroad
https://silkyroad.developpez.com/VBA/ClasseursFermes/#LIV-B
'Updates the value of "Field4" if "Field2" matches the variable "theName"
strSQL = "UPDATE [&" & Sheet & "$] SET " & _
"Field4 = " & UnitPrice & " WHERE Field2 = '" & theName & "'"
--
The forum is based on knowledge sharing: I do not respond to technical questions via private message.
Best regards, MichelHello,
It depends on your first file, what is its source, what type is it? Is it an Excel spreadsheet?
Give more detailed explanations
See you later
--
Experience teaches more surely than advice. (André Gide)
If you hit a pot and it sounds hollow, it’s not necessarily the pot that is empty. ;)-(Confucius)Microsoft Office Excel Fichier Valeurs Séparées par des Virgules ?
What version of Excel?
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it doesn't necessarily mean the pot is empty. ;-) (Confucius)Hello Polux,
I'm interested to know how to write in the closed workbook :-)
a) Replace a line.
b) Add after the last line.
Instead of where you put .. the query
Thanks in advance.
See you soon
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)-
Hello hermit,
Here is an example to add a line
Sub InsertRecord(ByVal nom As String, Byval prenom As String, ByVal age As Integer) Dim Cnx As ADODB.Connection Dim Fichier As String, Feuille As String, strSQL As String Fichier = "C:\maBase.xls" 'full path of the closed file Feuille = "Adhérants" 'Sheet where the data should be inserted Set Cnx = New ADODB.Connection With Cnx .Provider = "MSDASQL" .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _ "DBQ=" & Fichier & "; ReadOnly=False;" .Open End With 'The data must be indicated in the same order as the fields in the database. strSQL = "INSERT INTO [" & Feuille & "$] " _ & "VALUES ( '" & nom "', '" & prenom & "', " & age & ")" Cnx.Execute strSQL Cnx.Close Set Cnx = Nothing End Sub
You can also modify a data in a cell based on a criterion. The query would have this form:
strSQL = "UPDATE [" & Feuille & "$] SET " & _ "Champ4 = " & age & " WHERE Champ2 = '" & nom & "'"
;o)
--
“What is well conceived is clearly stated, And the words to say it come easily.”
Nicolas Boileau
Polux, Michel, Thank you for all these examples.
I had already found how to read but I couldn’t write in the closed workbook.
I also found the last link given by Michel but I didn't want to mix ADO and SQL yet, one thing at a time :-)
Thanks again to both of you.
See you later
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it’s not necessarily the pot that is empty. ;-) (Confucius)-
Hello hermit,
You're welcome, it's also by using the link provided by Michel that I created a module allowing me to read and write in a closed workbook. However, the closed workbook must be coherent. For example, no merged cells, the inserted data must be of the same type as the format of the cells in the closed workbook, etc.
Have a good Sunday.
;o)
--
“What we conceive well is clearly expressed, And the words to say it easily come.”
Nicolas Boileau
Hello everyone,
I have a workbook that contains several sheets and I want each sheet to contain one or more tables. I want to select a field, for example "B9", in the sheet "productionjour" and display it in an interface that I created myself with VB6. If someone could give me the procedure, I would be grateful.
-
