VBA to import selected columns to Access

[jhrBanker]

I receive an Excel file every day with 3 sheets (Removed, StartDt, Scheduling) which I need to import into 3 Access2007 tables with the same names. I want to use a command button to import the data and append the Excel records to the tables.



However:
I do not want to import all of the columns in all of the sheets.
I want to add the filename to each imported record in all 3 tables.
I want to be able to manipulate some of the data as it's imported. For example, if the value in column C of the "Removed" sheet is an "A", I want to import the value "Alpha", if "B" I want to import the value "Beta", etc.

I don't want to have to link the individual sheets each day and use a query. I want to be able to do this with VBA code.

Does anyone have example code that they can share?

Thanks,

[ssanfu]

I was having to do this a couple of years ago. Start by looking at this site:
http://www.accessmvp.com/kdsnell/EXCEL_Import.htm

You will need two loops. The outer will step through the rows and the inner will step through the columns. within the inner loop, you will have (I had) a Select Case to know which cell to modify the read in value...if "B" I want to import the value "Beta". You will have a lot of variables.

If/when you get stuck, post back.....

[jhrBanker]

I'm assuming you are referring to the section/code "Write Data From an EXCEL Worksheet into a Recordset using Automation (VBA)"

You've stated that I will need 2 Loops, one for the Rows and one for the Columns. Can you provide some help with the inner Loop assuming that I need to take the value from column A into tablefield "Value1", column B into tablefield "Value2", and column F into tablefield "Value3".

Do While xlc.Value <> ""
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 1
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1,0)
Loop

Thanks for your help.

[ssanfu]

Took me a while to find the code. We are now getting out data in CSV text files.

In the following code, I cut out a lot of processing and extra lines I couldn't post. Because people have apostrophes in their names, I use a function to double up on the apostrophes

 Function ConvertQuotesSingle(InputVal)
   ConvertQuotesSingle = Replace(InputVal, "'", "''")
End Function

My full procedure is about 12 pages long. Here is cut down version to illustrate the inner and outer loops.

I tried putting the code inline, but the post became huge. So it is an attachment.

I would paste the code into a module and look at it..... just a suggestion.... for me the color coding enhances my ability to understand the code.

Don't forget the function above...