How to run this code?

[compooper]

Hi,

Let me just start by saying, I am completely lost when using VBA.
I am heavily relying on sample codes and databases, but I do not understand how to test the following code:
http://www.rlmueller.net/Programs/ReadCSV.txt

Could someone please explain how?

[jgelpi16]

What this code is takes a string of comma-separated values ("one,two,three,four") and dumps then into an array. An array is a "list" of values. If you can imagine a single-column table. You reference the array by telling the array which "row" to return. What I recommend doing is calling this function as I have shown below. I also recommend you step through the code using the F8 key so you can understand a bit more about it.

Function funCallCSVParse()
Call CSVParse("one,two,three,four")
End Function

Function CSVParse(ByVal strLine)
'Copy/Paste your parse function code here
'.....
'Replace CSVParse = arrFields with the below code so you can see what is being returned
MsgBox arrFields(0), vbOKOnly
MsgBox arrFields(1), vbOKOnly
MsgBox arrFields(2), vbOKOnly
MsgBox arrFields(3), vbOKOnly
End Function

I hope this helps.

[jgelpi16]

I guess the next question is....What are you trying to do with this? Is it what you are looking for? Is there an easier way?

[compooper]

Oh Perfect.
I was able to see the msgboxes with the correct data.

What I am actually trying to do is take files and only import select fields.
The field headers are in the third/second row depending the file.
& from there I want to write a code that only imports certain fields.

[jgelpi16]

What format will these files be in? Will the fields be criteria driven, or static? (i.e. in Excel import cells B1:C3 OR import any cell that has a value of "True")

[compooper]

The majority of the files are excel workbooks.
The fields are in a consistent location, the field headers start in the second/third row depending on the file.

So for example Client Name will always be in column A, but if one day it does change, I do not want the system to collapse.
So it may be better if the code searches for the header names and then takes the corresponding data.

[jgelpi16]

Hmmmm...Can you attach a picture of one of your spreadsheets? If I am visualizing this correctly you would have something like the attached screenshot. Is this correct? IF this is the case what is the harm in importing everything and then using a recordset to extract what you need? It is entirely possible there is an easier way but I am not well versed in VBA for Excel just yet.

[compooper]

The only difficulty I see in importing each one is storing uneccesary tables in my access database.
I'll be importing up to 20 tables per month, with thousands of records in each.

So first upload file - then run vba code/queries to extract information - double store in another table.

Would it be easier to just extract the information from the get go?

[jgelpi16]

Well it may be easier to extract from get-go. What I was getting at is you could import the entire spreadsheet, extract the data using a DAO recordset and then simply purge all the data in "import" table. So you just have one table that you are working with in Access, not hundreds. The only tricky piece is the column headers must match. You can do one of two things....Leave the columns un-named (not recommended) and just have them import as access default (F1, F2, F3, etc...). OR you can write a function to add column headers (values in row A in Excel) and then run that format macro before importing each sheet. Below is an example of how you would do that...

Function IsExcelRunning() As Boolean
    Dim xlApp As Excel.Application
On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    IsExcelRunning = (Err.Number = 0)
    Set xlApp = Nothing
    Err.Clear
End Function

Function funExportBatch()
    Dim strFileLoc As String, strFileName As String, strFileExt As String, strTableName As String
    Dim xlApp As Object
    Dim xlSheet As Object
    Dim ExcelRunning As Boolean
    
    ExcelRunning = IsExcelRunning()
    
    If ExcelRunning Then
        Set xlApp = GetObject(, "Excel.Application")
    Else
        Set xlApp = CreateObject("Excel.Application")
    End If
    strFileLoc = "C:\MyData\"
    strFileName = "FileName"
    strFileExt = ".xlsx"	'This is optional.  I splice my extensions out because I tend to manipulate the file name a lot.

    Set xlSheet = xlApp.Workbooks.Open(strFileLoc & strFileName & strFileExt).Sheets(1)
    With xlApp.Application.Selection
        .Range("J1").Select
        .ActiveCell.FormulaR1C1 = "Column1"
        .Range("K1").Select
        .ActiveCell.FormulaR1C1 = "Column2"
        .Range("N1").Select
        .ActiveCell.FormulaR1C1 = "Column3"
    End With
    With xlApp.Application
        .ActiveWorkbook.Save
        .ActiveWorkbook.Close
    End With
    xlApp.Quit

    Set xlApp = Nothing
    Set xlSheet = Nothing

[compooper]

Okay so I'm trying to use your code, but I'm getting an error.
User-Defined Type not Defined on the line Dim xlApp As Excel.Application

I'm using Access 2003 by the way

[jgelpi16]

You need to make sure you add the Excel reference libraries to your module (Tools -> References...). You'll need to add Microsoft Excel #.0 Object Library.

[compooper]

Okay got it, but now i get:
Object doesn't support this property or method on:
.Range("B2").Select

[jgelpi16]

make sure you have the below selected as "Available References:"

-Visual Basic For Applications
-Microsoft Access #.0 Object Library
-OLE Automation
-Microsoft Office #.0 Access database engine Object Library
-Microsoft Excel #.0 Object Library

[compooper]

I don't seem to have Microsoft Office #.0 Access database engine Object Library
in my list.
I have the following checked though:
-Vidual Basic For Applications
-Microsoft Access 11.0 Object Library
-OLE Automation
-Microsoft DAO 3.6 Object Library
-Microsoft AvtiveX Data Objects 2.1 Library
-Microsoft Excel 11.0 Object Library
-Microsoft Office 12.0 Object Library

Still getting the error

[compooper]

Okay so I took the .Selection off of With xlApp.Application
& I think its working since I'm getting no errors, but once again I'm not exactly sure how to test this code.