i would probably use sql to query the data and import
post some sample data
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
es SQL would probably be easier but that would involve setting up a database which is not possible.
you can also query against a worksheet
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
this is just proof of concept, using sql in VBA, you will need a reference to ADO (activex data objects)
Code:
Sub csvsql()
Dim cn As Connection, rs As Recordset, cat As ADOX.Catalog
Set cn = New Connection
Set rs = New Recordset
Set cat = New Catalog
mypath = "w:\.team documents\freehold team\freeholders managers\reporting\reports\"
With cn
.Provider = "microsoft.ace.oledb.12.0;data source=" & mypath & ";" & _
"Extended properties='text;hdr=no;fmt=csvdelimited'"
.Open
End With
rs.Open "select * from Insurance Ledger.csv where Insurance Type Buildings - Property Owners = 'john' ", cn ' as it contains spaces the filename, it may require brackets
' you can use a where clause in the above line to do the filtering on multiple columns
Sheets("insurance").Range("a1").CopyFromRecordset rs 'copy the recordset to whatever desired cell
rs.close
cn.close
End Sub
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
faster than looping cells and does not in any way change any data in worksheet, selecting on multiple criteria much easier
downside is you can not copy any formatting, only data
i tested this, appeared to work correctly, returned 3 records
Code:
With cn
.Provider = "microsoft.ace.oledb.12.0;data source=" & ActiveWorkbook.FullName & ";" & _
"Extended properties='Excel 12.0; header = yes'"
' .Provider = "microsoft.ace.oledb.12.0;data source=" & mypath & ";" & _
"Extended properties='text;hdr=no;fmt=csvdelimited'"
.Open
End With
rs.Open "select * from [Insurance Ledger$] where [Insurance type]='Buildings - Property Owners' and (Status='Active'or Status='Scheduled') ", cn
Sheets("insurance").Range("a1").CopyFromRecordset rs
note as the sample data is in a worksheet now, not .csv, i had to change the connection string, should work fine with previous connection string, commented out above
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
yes it would require a reference, BUT
you can delete that line, as while i was using a catalog for testing, is not used in the finished code
you must also delete the line Set cat = New Catalog, as it will also error
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Ok I'm getting an error message on this line..... - No value given for one or more required parameters
rs.Open "select * from [Insurance Ledger.csv] where [Type]='Buildings - Property Owners' and [Status]='Active' or [Status]='Scheduled'", cn
Sub Insurance()
Dim cn As Connection, rs As Recordset
Set cn = New Connection
Set rs = New Recordset
mypath = "W:\.Team documents\Freehold team\Freehold managers\Reporting\Reports"
With cn
.Provider = "microsoft.ace.oledb.12.0;data source=" & mypath & ";" & _
"Extended properties='text;hdr=no;fmt=csvdelimited'"
.Open
End With
rs.Open "select * from [Insurance Ledger.csv] where [Type]='Buildings - Property Owners' and [Status]='Active' or [Status]='Scheduled'", cn
please always use code tags when posting code, much better for me to see
i tested all the code with the sample file you posted, but that was using a worksheet for the source data as supplied in the sample
i had done some other testing on .csv files and would expect that to with the code as posted directly above,
if you post a .csv, i can test with that, i will not create a .csv file here for testing as it may not be a correct sample (been there, done that)
i just noticed you are missing the required brackets on the status alternatives, put them in and test again
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Is it better to use "microsoft.Jet.oledb. rather than microsoft.ace.oledb for csv files? Does it depend on what version of Excel I'm running?
jet is the older version and may not work with all versions of excel, but probably no difference for .csv files
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
here is a tested code using your .csv source file, i was of course using a different path
Code:
Dim cn As Connection, rs As Recordset
Set cn = New Connection
Set rs = New Recordset
mypath = "w:\.team documents\freehold team\freeholders managers\reporting\reports\"
With cn
.Provider = "microsoft.ace.oledb.12.0;data source=" & mypath & ";" & _
"Extended properties='text;hdr=yes;fmt=csvdelimited'"
.Open
End With
rs.Open "select * from [Insurance example.csv] where [Insurance type]='Buildings - Property Owners' and (Status='Active'or Status='Scheduled') ", cn
Sheets("insurance").Range("a1").CopyFromRecordset rs
rs.close
cn.close
the code tags around the code improve the appearance and readability of the code
Last edited by westconn1; Dec 9th, 2020 at 03:41 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
The microsoft access database engine cannot open or write to the file ". It is already opened exclusively by another user , or you need permission to view and write its data.
rs.Open "select * from [Insurance example.csv] where [Insurance type]='Buildings - Property Owners' and (Status='Active'or Status='Scheduled') ", cn
do you or someone else already have the file open?
it is a very specific error, unlike the previous one
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
with Sheets("insurance").Range("a1")
for col = 0 to rs.fields.count
.offset(,col) = rs.fields(col).name
next
.offset(1).copyfromrecordset rs
end with
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Apologies for any confusion. I'm not certain what you're referring to regarding posting with code tags.
I've included a new zip file containing a CSV file for your testing purposes.
Regarding your question about using "Microsoft.Jet.OLEDB" versus "Microsoft.ACE.OLEDB" for CSV files, the choice may depend on the version of Excel you're using. "Microsoft.Jet.OLEDB" is generally used for older versions of Excel (prior to 2007), while "Microsoft.ACE.OLEDB" is typically used for newer versions (2007 and later). However, both can work for CSV files.
Reply With Quote
