MS Excel VBA |

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 […]

Excel VBA Read Text File

So how can you import the contents of a csv or text file into your Excel worksheet: Find out:

'========================================================
'DESIGNED &amp; 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(&quot;Text File Contents&quot;).Select
    Range(&quot;A2:K6500&quot;).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 = &amp;H1
   
    Set s_cnn = New ADODB.Connection

    strToolWkbk = &quot;read_file.txt&quot;
    strPath = ThisWorkbook.Path &amp; &quot;\read_file&quot;

    Debug.Print strPath
    
    
    'For a text file, Data Source is the folder, not the file
    s_cnn = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; &amp; strPath &amp; &quot;;&quot; _
    &amp; &quot;Extended Properties=&quot;&quot;text;HDR=Yes;FMT=Delimited&quot;&quot;;&quot;
    
    s_cnn.Open
    Set s_rst = New ADODB.Recordset
    

    strSQL = &quot;SELECT * FROM &quot; &amp; strToolWkbk

        
    s_rst.Open strSQL, _
        s_cnn, adOpenStatic, adLockOptimistic, adCmdText

    intRow = 2
    
    s_rst.MoveFirst
    

    Do Until s_rst.EOF
        Range(&quot;A&quot; &amp; intRow) = s_rst(0)
        Range(&quot;B&quot; &amp; intRow) = s_rst(1)
        Range(&quot;C&quot; &amp; intRow) = s_rst(2)
        Range(&quot;D&quot; &amp; intRow) = s_rst(3)
        Range(&quot;E&quot; &amp; intRow) = s_rst(4)
        Range(&quot;F&quot; &amp; intRow) = s_rst(5)
        Range(&quot;G&quot; &amp; intRow) = s_rst(6)
        Range(&quot;H&quot; &amp; intRow) = s_rst(7)
        Range(&quot;I&quot; &amp; intRow) = s_rst(8)
        Range(&quot;J&quot; &amp; intRow) = s_rst(9)

        intRow = intRow + 1
        s_rst.MoveNext
    Loop
    
    Range(&quot;A:C&quot;).Select
    Selection.NumberFormat = &quot;General&quot;
    
    Range(&quot;D:D&quot;).Select
    Selection.NumberFormat = &quot;m/d/yyyy&quot;
    
    Range(&quot;E:K&quot;).Select
    Selection.NumberFormat = &quot;General&quot;
    
    Range(&quot;A1&quot;).Select
    MsgBox &quot;DONE.&quot;
    
    s_rst.Close
    s_cnn.Close
    
    Set s_rst = Nothing
    Set s_cnn = Nothing
        

End Sub

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 […]

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 – […]