VBA EXCEL read file without opening it
Kree
-
Kree -
Kree -
Bonjour,
I am looking for help to read data from a file without opening it and import that data into Excel. My Excel file (Let's call it XL) indirectly contains the name of the file where my data is located (Let's call it DN) via different columns (let's say DN= Range ("A1") & "_a_" & Range("A2"), with a consistent path like v:\..\DN).
The problem is that DN is not an .xls file. When I try to open it with Excel, I can retrieve the data I want to work with that is always in the same column. Therefore, I would like to know if it is possible to create a macro that allows me to read DN as an Excel document without opening it.
Note: DN is a .dbf file.
This would allow me to include a formula like ="sumprod(substitute('v:\..\[DN]sheet1'!$B2:$B1065,".","")*1)" and instantly get the sum in XL without having to open/close DN in a new Excel document.
As a novice in VBA, I am asking for your help, at least to know if this is possible. I was also wondering if it was possible to directly get the location of the data under dbf and transpose the "." into "," to get usable numbers in Excel (I have tried changing my decimal separator in Excel, but that does not solve this issue), if possible, directly calculating the sum of these "123.456" type data.
I hope I have been clear enough and I look forward to some guidance. Sorry to make you work on this May 1st...
Thank you.
I am looking for help to read data from a file without opening it and import that data into Excel. My Excel file (Let's call it XL) indirectly contains the name of the file where my data is located (Let's call it DN) via different columns (let's say DN= Range ("A1") & "_a_" & Range("A2"), with a consistent path like v:\..\DN).
The problem is that DN is not an .xls file. When I try to open it with Excel, I can retrieve the data I want to work with that is always in the same column. Therefore, I would like to know if it is possible to create a macro that allows me to read DN as an Excel document without opening it.
Note: DN is a .dbf file.
This would allow me to include a formula like ="sumprod(substitute('v:\..\[DN]sheet1'!$B2:$B1065,".","")*1)" and instantly get the sum in XL without having to open/close DN in a new Excel document.
As a novice in VBA, I am asking for your help, at least to know if this is possible. I was also wondering if it was possible to directly get the location of the data under dbf and transpose the "." into "," to get usable numbers in Excel (I have tried changing my decimal separator in Excel, but that does not solve this issue), if possible, directly calculating the sum of these "123.456" type data.
I hope I have been clear enough and I look forward to some guidance. Sorry to make you work on this May 1st...
Thank you.
5 answers
Hello,
an example that inserts into your workbook the data from a field of a DBF database using ADO technology
in bold elements to customize
Sub import_fieldDBF()
'requires activating the Microsoft ActiveX Data Objects x.x Library reference (in VBE tools-references)
Dim source As ADODB.Connection
Dim Query As ADODB.Recordset
Dim Path As String, file As String, sql_text As String
Path = "F:\database location"
'Path = ThisWorkbook.Path if your database is in the same directory
file = "database.dbf"
Set source = New ADODB.Connection
source.Open _
"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" & _
Path & ";"
sql_text = "SELECT yourfield FROM " & file & ";" 'if you import everything put * instead of yourfield
Set Query = New Recordset
Query.Open sql_text, source, adOpenKeyset, adLockOptimistic
If Query.EOF Then
MsgBox "Empty table..."
Exit Sub
End If
Application.ScreenUpdating = False
Range("B2").CopyFromRecordset Query
Query.Close
source.Close
End Sub
based on MichelXLD at www.excel-downloads.com
Michel
an example that inserts into your workbook the data from a field of a DBF database using ADO technology
in bold elements to customize
Sub import_fieldDBF()
'requires activating the Microsoft ActiveX Data Objects x.x Library reference (in VBE tools-references)
Dim source As ADODB.Connection
Dim Query As ADODB.Recordset
Dim Path As String, file As String, sql_text As String
Path = "F:\database location"
'Path = ThisWorkbook.Path if your database is in the same directory
file = "database.dbf"
Set source = New ADODB.Connection
source.Open _
"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" & _
Path & ";"
sql_text = "SELECT yourfield FROM " & file & ";" 'if you import everything put * instead of yourfield
Set Query = New Recordset
Query.Open sql_text, source, adOpenKeyset, adLockOptimistic
If Query.EOF Then
MsgBox "Empty table..."
Exit Sub
End If
Application.ScreenUpdating = False
Range("B2").CopyFromRecordset Query
Query.Close
source.Close
End Sub
based on MichelXLD at www.excel-downloads.com
Michel
Hello Michel,
Wow, I don't understand everything about the code and I don't have the means to test it today, but it seems complex enough to work...
And here I was looking for a simple code; I could have waited ages before having a draft of the solution.
If I understand correctly, I'm importing my entire database or rather the range I want but can't perform an operation on it.
And if I still understand correctly, Source also opens up, but there I'm not sure I get it because it would be simpler to open it as an Excel file, right?
In any case, thank you very much for the help, I'll test the code as soon as possible to really see what it does and try to understand a bit better how it works.
Wow, I don't understand everything about the code and I don't have the means to test it today, but it seems complex enough to work...
And here I was looking for a simple code; I could have waited ages before having a draft of the solution.
If I understand correctly, I'm importing my entire database or rather the range I want but can't perform an operation on it.
And if I still understand correctly, Source also opens up, but there I'm not sure I get it because it would be simpler to open it as an Excel file, right?
In any case, thank you very much for the help, I'll test the code as soon as possible to really see what it does and try to understand a bit better how it works.
re,
well... if the source is a dbf, it's not an xls
you connect to Source but you don't open it because it's an indexed sequential file; SQL allows you to access source data very quickly (see example secu file or your bank)
you can perform calculations (operations?) with SQL syntax
for example
SELECT [nbre]*[px_unit] AS total FROM....
creates a field "total" by multiplying the values of the number and unit price fields...
if you want to filter on a variable you add WHERE name="toto"
I admit that it's not necessarily easy and that every time, I end up struggling quite a bit with Occitan choirs.
well... if the source is a dbf, it's not an xls
you connect to Source but you don't open it because it's an indexed sequential file; SQL allows you to access source data very quickly (see example secu file or your bank)
you can perform calculations (operations?) with SQL syntax
for example
SELECT [nbre]*[px_unit] AS total FROM....
creates a field "total" by multiplying the values of the number and unit price fields...
if you want to filter on a variable you add WHERE name="toto"
I admit that it's not necessarily easy and that every time, I end up struggling quite a bit with Occitan choirs.
Oh, it really starts to go beyond my understanding, initially I just wanted to simplify my life a bit, but with the problem launched, I believe solving it will complicate things...
OK (or pretty much) for SQL calculations and I believe (I take your word for it) in SQL speed.
I was opening my dbf like this:
Dim AppXL As Excel.application
Dim ClassR As Excel.workbook
Dim Feuye As Excel.worksheet
Set AppXL = createObject("Excel.application")
Dim a As string
a= Range("A1") & "_a_" & Range("A2") 'Name of my file
Dim b As string
b = "v:\..\" & a
Workbook.opentext filename: = b
And my dbf would appear as an Excel workbook in which I could quietly play with my cells before closing it.
The main concern was the time it took to open and close an Excel document for each dbf file reference.
In conclusion, if I want to gain speed, I need to learn SQL in addition to VBA...
OK (or pretty much) for SQL calculations and I believe (I take your word for it) in SQL speed.
I was opening my dbf like this:
Dim AppXL As Excel.application
Dim ClassR As Excel.workbook
Dim Feuye As Excel.worksheet
Set AppXL = createObject("Excel.application")
Dim a As string
a= Range("A1") & "_a_" & Range("A2") 'Name of my file
Dim b As string
b = "v:\..\" & a
Workbook.opentext filename: = b
And my dbf would appear as an Excel workbook in which I could quietly play with my cells before closing it.
The main concern was the time it took to open and close an Excel document for each dbf file reference.
In conclusion, if I want to gain speed, I need to learn SQL in addition to VBA...
re,
Yes, but if you say to start with VBA, use what you know and understand while discovering new techniques; you now know that it is possible to read or write to a closed file, you will be 1 out of 4 to approach these concepts... We use ADO-SQL for large files or to consolidate N data from P files into a summary.
For me, it didn't come immediately, and even now, I hesitate and often mess up (9/10)
Best regards
Michel
Yes, but if you say to start with VBA, use what you know and understand while discovering new techniques; you now know that it is possible to read or write to a closed file, you will be 1 out of 4 to approach these concepts... We use ADO-SQL for large files or to consolidate N data from P files into a summary.
For me, it didn't come immediately, and even now, I hesitate and often mess up (9/10)
Best regards
Michel
re,
What I know is that I still have a lot to understand and that the safest way to move forward is to take it step by step.
What I believe I understand: when I ask to open my file with the command workbook.opentext filename:=b, I create said workbook by opening it, which implies that, on one hand, I create workbook b (or rather "a" in my example) and, on the other hand, I open it, all in one command.
What I don't understand is why I can't simply create it and reference it (there must surely be a technique to discover). Because, once created, I should be able to access its content and do my calculations without having to open it (which I know how to do with a regular .xls as long as the path is known). This is what I am beginning to understand as illusory.
- Where does my reasoning bug? - Likely in my VBA principles and the potential of ADO-SQL (which is still very obscure) for what I am trying to achieve.
In any case, a big thank you for the SQL lead that I will test and for the rest.
What I know is that I still have a lot to understand and that the safest way to move forward is to take it step by step.
What I believe I understand: when I ask to open my file with the command workbook.opentext filename:=b, I create said workbook by opening it, which implies that, on one hand, I create workbook b (or rather "a" in my example) and, on the other hand, I open it, all in one command.
What I don't understand is why I can't simply create it and reference it (there must surely be a technique to discover). Because, once created, I should be able to access its content and do my calculations without having to open it (which I know how to do with a regular .xls as long as the path is known). This is what I am beginning to understand as illusory.
- Where does my reasoning bug? - Likely in my VBA principles and the potential of ADO-SQL (which is still very obscure) for what I am trying to achieve.
In any case, a big thank you for the SQL lead that I will test and for the rest.