Matrix, array, loop in VBA ?

[treloj]

Hi,

I need help for resolving this kind of problem, because I just start with VBA. I found and modify solution to export query from MS Access (using VBA) to separate workbooks in Excel so data can be filled in. Structure of table is simple

idTest (int)
Test1
Test2

In each exported workbook I insert few new rows with date columns (dd/mm/yy) and two fields under each date (fill1, fill2 (int, filled by someone else)) so now, my workbook have look something like:

+++++++++++++++|***date1***|***date2***| etc....
+++++++++++++++| fill 1 | f ill2 | fill 1 | fill2 | etc. ...
idTest | Test1|Test2| xxxx |xxxxx | xxxx | xxxxx | etc. ...
etc....

Well, now problems begin...

I must read each workbook so I can generate text file with this fields:

idTest|date1| fill 1|fill 2|
idTest|date2| fill 1| fill 2|
etc...

I am not good with VBA but I know that I have to read each idTest until last (eof or blank) and that will be a For loop, but for one idTest I must check whole Date(1-n) (that will be another For loop inside idTest loop) and read and add fill 1 and fill 2 for each idTest|Date row...

I try to find example to see structure and understand how I can do it for my case but there are only simple examples with using array, loops etc. I don't know how to use them together to solve problem which I have.
If someone have time to explain and show how to do that it will be nice.

I decide to create button on Form in Access which will onClick look in C:/test/ *.xls and read each xls data that I need and then put all data as I need in new table in Access or txt file.

If You think that I can resolve this problem on different way I will be glad to listen.

Thank You All,

Treloj

 

[Roku]

The mechanism you want is something like this in Excel VBA:

Option Explicit
Private Const kExtract As String = "Extract", kSource As String = "Source"
Private wb As Workbook, s1 As Worksheet, s2 As Worksheet
Private c1 As Integer, r1 As Integer, r2 As Integer
Private Sub extractFields()
Set wb = ThisWorkbook
Rem create extract worksheet
Set s2 = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
s2.Name = kExtract
r2 = 0
Set s1 = wb.Worksheets(kSource)
For r1 = 3 To s1.UsedRange.Rows.Count
  For c1 = 2 To s1.Columns.Count Step 2
    Rem ensure next source field exists
    If IsEmpty(s1.Cells(r1, c1)) Then Exit For
    Rem next output row
    r2 = r2 + 1
    Rem copy index from column 1 of current source row
    s2.Cells(r2, 1) = s1.Cells(r1, 1)
    Rem copy the date from row 1 of current column of current source row
    s2.Cells(r2, 2) = s1.Cells(1, c1)
    Rem copy the two fill fields from current source row
    s2.Cells(r2, 3) = s1.Cells(r1, c1)
    s2.Cells(r2, 4) = s1.Cells(r1, c1).Offset(0, 1)
  Next
Next
Rem transfer extracted fields to text file
Open "g:\output.txt" For Output As #1
For r2 = 1 To s2.UsedRange.Rows.Count
  Write #1, s2.Cells(r2, 1) & "," & s2.Cells(r2, 2) & "," & s2.Cells(r2, 3) & "," & s2.Cells(r2, 4)
Next
Close #1
'Rem remove the extracted worksheet
'Application.DisplayAlerts = False
's2.Delete
'Application.DisplayAlerts = True
End Sub

In the VBA Editor, create a new module and paste this code into it; then run the procedure manually.

This method uses an intermediate worksheet to get the results into a logical view for you; however, you could write to the text file in-line if you prefer.

I'm not clear about where you actually want to put the output - Excel, Access or Text file. If your objective is to create an Access table, the logic is similar but done in Access instead of Excel. The application-specific code would have to be re-worked, but that's not difficult. Please advise if you want the output in Access from an Excel source.

 

[treloj]

Thank You Roku,

I will try this weekend Your code. Today I check once again whole logical operations which I have in my problem and now when I look at Your comments inside code, where You explain what and why, it look's very, very similar, but with one exception, I am beginner in VBA and You are few light yrs away

Export can be in *.txt as You did it.

Now I will read step by step execution of Your code to clearly understand what and how it works. Of course, I will post here, if I need to modify or add detail in code.

Once again, thank You !

Treloj

The mechanism you want is something like this in Excel VBA:

Option Explicit
Private Const kExtract As String = "Extract", kSource As String = "Source"
Private wb As Workbook, s1 As Worksheet, s2 As Worksheet
Private c1 As Integer, r1 As Integer, r2 As Integer
Private Sub extractFields()
Set wb = ThisWorkbook
Rem create extract worksheet
Set s2 = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
s2.Name = kExtract
r2 = 0
Set s1 = wb.Worksheets(kSource)
For r1 = 3 To s1.UsedRange.Rows.Count
  For c1 = 2 To s1.Columns.Count Step 2
    Rem ensure next source field exists
    If IsEmpty(s1.Cells(r1, c1)) Then Exit For
    Rem next output row
    r2 = r2 + 1
    Rem copy index from column 1 of current source row
    s2.Cells(r2, 1) = s1.Cells(r1, 1)
    Rem copy the date from row 1 of current column of current source row
    s2.Cells(r2, 2) = s1.Cells(1, c1)
    Rem copy the two fill fields from current source row
    s2.Cells(r2, 3) = s1.Cells(r1, c1)
    s2.Cells(r2, 4) = s1.Cells(r1, c1).Offset(0, 1)
  Next
Next
Rem transfer extracted fields to text file
Open "g:\output.txt" For Output As #1
For r2 = 1 To s2.UsedRange.Rows.Count
  Write #1, s2.Cells(r2, 1) & "," & s2.Cells(r2, 2) & "," & s2.Cells(r2, 3) & "," & s2.Cells(r2, 4)
Next
Close #1
'Rem remove the extracted worksheet
'Application.DisplayAlerts = False
's2.Delete
'Application.DisplayAlerts = True
End Sub

In the VBA Editor, create a new module and paste this code into it; then run the procedure manually.

This method uses an intermediate worksheet to get the results into a logical view for you; however, you could write to the text file in-line if you prefer.

I'm not clear about where you actually want to put the output - Excel, Access or Text file. If your objective is to create an Access table, the logic is similar but done in Access instead of Excel. The application-specific code would have to be re-worked, but that's not difficult. Please advise if you want the output in Access from an Excel source.

 

[treloj]

Hi,

I create Module as You said, with code You write but when I run manual it show Run-time error 1004, Method 'ThisWorkobook' of object '_Global' failed...

I am not sure what to do ?

I have now two options, to use enum (to list *.xls file inside d:\test\) to form a list of wb which will be readed by code You write or to use something else like

Private Function Files(trace As String) As String()
Dim a As String
Dim b() As String
Dim i As Integer
a = Dir(trace & "\*.xls", vbNormal)
i = 1
Do While a <> ""
If InStr(1, a, ".xls", vbTextCompare) > 0 Then
ReDim Preserve b(i)
b(i - 1) = a
i = i + 1
End If
a = Dir
Loop
Files = b
End Function

Private Sub Command1_Click()
Dim fajl() As String
Dim i As Integer
file = Files("d:\test\")
For i = 0 To UBound(file) - 1
Debug.Print file(i)
End If
End Sub

With this I can read whole d:\test\ and have list of xls files, but now I have problem to continue with code You write. What I have to do ?
You see that I have workbook(s) with only one worksheet and they are separated *.xls files. Obiously, if I read first listed xls then I must call Your module to do work inside that file (worksheet) and when it receive end loop(for empty row in ws of each xls), he will go to the next xls and do same until last xls file in folder.

My idea is to with one button clik on Form in Access, create data list in d:\test\*.xls (not visible to user) and then (with code You write) transfer data (for all xls) in one new table in Access or simple txt file with field structure as I describe.
So, how can I do this and of course what I need to learn and know, so it can became functional ?

As You can see, I don`t expect that anyone here do it for me, I just ask for help so I can help someone else to learn what I did.

Thank You

 

[Roku]

Where did you put the module you created for my code? This is an Excel VBA module, to be put in the workbook of file names after export from Access. I don't see any obvious reason for the error you have.

I note you use .xls as you Excel file extension - which version of Office to you have? My code was written for Office 2007, but there's nothing there which would not work for Office 2003.

From your description, it seems to me that you should not be using Excel at all. Whilst it is possible to do what you describe, it is not the most efficient method. I suggest you work entirely within Access, then adapt my Excel VBA for use with Access VBA. The worksheet rows are the equivalent of Table records, so you need to be looking at RecordSet Fields in place of Worksheet Cells. This code would be invoked from your Access Form and would not be visible to the user.

If you need more help with any of this, please ask - but be specific about the context in which the code is to be used.

 

[treloj]

Roku,

thank You for help, I did everything You wrote here and now it works under Excell 2007 as charm. I`m sorry for my late answer here, but I have accident and broke my small finger so, now I have plaster cast on my finger and hand....and now became very difficult to work with keyboard...

Bye,

Treloj