MS Excel VBA
Excel VBA Read Text File
So how can you import the contents of a csv or text file into your Excel worksheet: Find out:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
'======================================================== 'DESIGNED & CODED BY LOEBLCOM SERVICES 2013 'ERIK LOEBL(713)409-7041 'EMAIL: erik@loeblcomservices.com 'WEB: http://loeblcomservices.com '======================================================== Sub query_text_file() 'PURPOSE: Query A Text File '************************************* 'clear 'Text File Contents' worksheet '************************************* Worksheets("Text File Contents").Select Range("A2:K6500").Clear '************************************* 'now populate copy the orders over '************************************* Dim strPath As String 'Need to reference the: ' Microsoft ActiveX Data Objects 2.5 Library Dim s_rst As ADODB.Recordset Dim s_cnn As ADODB.Connection 's for sub connection Dim intRow As Integer Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adCmdText = &H1 Set s_cnn = New ADODB.Connection strToolWkbk = "read_file.txt" strPath = ThisWorkbook.Path & "\read_file" Debug.Print strPath 'For a text file, Data Source is the folder, not the file s_cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";" _ & "Extended Properties=""text;HDR=Yes;FMT=Delimited"";" s_cnn.Open Set s_rst = New ADODB.Recordset strSQL = "SELECT * FROM " & strToolWkbk s_rst.Open strSQL, _ s_cnn, adOpenStatic, adLockOptimistic, adCmdText intRow = 2 s_rst.MoveFirst Do Until s_rst.EOF Range("A" & intRow) = s_rst(0) Range("B" & intRow) = s_rst(1) Range("C" & intRow) = s_rst(2) Range("D" & intRow) = s_rst(3) Range("E" & intRow) = s_rst(4) Range("F" & intRow) = s_rst(5) Range("G" & intRow) = s_rst(6) Range("H" & intRow) = s_rst(7) Range("I" & intRow) = s_rst(8) Range("J" & intRow) = s_rst(9) intRow = intRow + 1 s_rst.MoveNext Loop Range("A:C").Select Selection.NumberFormat = "General" Range("D:D").Select Selection.NumberFormat = "m/d/yyyy" Range("E:K").Select Selection.NumberFormat = "General" Range("A1").Select MsgBox "DONE." s_rst.Close s_cnn.Close Set s_rst = Nothing Set s_cnn = Nothing End Sub |
Click here for the file: VBA Read Excel File.zip
VBA Read Excel File
Here is a question I received “Thanks for the video, I have a question.How to read from a group of closed excel files searching for a certain value in say column F then if that value is found then copy that row to a workbook and continue search for then next value ” So I […]
How To Do An Access Export To Excel In VBA
In this post, we are going to do an Access export to Excel, and you’ll see the VBA we’ll work with. This Excel automation will create a worksheet for each of our data points, and then enter the row items for that data point. We are going to let Access do most of the querying […]
Field Inspection App In MS Access
Today, we will consider how to track field inspections, by building a framework for a field inspection app in MS Access. This sample application is just a framework, and just shares some potential ideas. You can customize it and put your own “spice” on it. Here’s what it will look like when we’re done: First […]
Create A Report Based On Items Selected In Access Combo Box
You can download the database here: Click here for the database and the code: Create-A-Report-Based-On-Items-Selected-In-Access-Combo-Box.accdb If you have a suggestion for a future video, contact me on my contact form, and I will seriously consider it.
Resolution – VBA Error 49 – Bad DLL Calling Convention
So everything in this particular database was working for a client of mine, until just last night I get notified that he can’t open the database because of some error. I was able to view his screen using T.V. and after clicking on a command button, I would get the following error: “Error 49 – […]