Help using VBA to assist Access table updating

[TylerTand]

I have recieved some suggestions for using VBA to perform the daily updating of my tables in access. The problem is I don't know how to interface VBA with Access. Can someone please explain this to me? I need to be able to update a large number of tables from excel everyday and somehow using VBA can help but I need some help getting started. I realize that people don't have time to do everything for me, but I could use a little help organizing this project. Thanks for your help.

 

[Guus2005]

Try this example database with some code.

HTH

 

[TylerTand]

Guus2005,

Thanks for the sample. The problem is that its entirely in greek to me. I added a sample table but don't know what to do from there. Can you give me some more help? Thanks

 

[Guus2005]

Have you ever created a form in Access?
Do you have any VBA experience?
Which version do you use?

 

[TylerTand]

I have minimal experience creating a form and no experience with VBA. I have version 2007. I don't really need a form, I need help with the VBA code to transferdata from Excel to Access on a large scale and automated. Manually updating 250 spreadsheet connections everyday isn't an option for me. Hopefully this helps you understand where I am coming from and you can help me. Thanks.

 

[Guus2005]

I can help but I won't do it for you.

You have to create a table with all spreadsheet paths. If it is a directory with 250 spreadsheets: dont bother. Use can use the dir command to loop through all spreadsheets.

Do they need to be imported in separate tables?

 

[TylerTand]

Thanks Guus2005,

I greatly appreciate your help. I'm not asking you to do the whole thing for me but just guide me along as I go. I understand completely. Everybody is busy. Anyway, the data nees to be in ONE table. So I can then run one query against all the data and get all the contracts that fit the criteria. For me I guess the first question is how to interface between Access, Excel and VBA? I would prefer to be able to run the VBA command from Access that would tell Access to take the contents of all the files in the folder named Ascii and combine them and put them in a Table named Data. I need to do this once and then run an append command to check and add the new row of information in the excel files to the Access Table. This would be run once a day. Then I can run my query on the table and it includes today's numbers. I was thinking of having a form that would display the results of my query. This page could have buttons to both update the data and run the query? I found this code and was wondering if you could help me de-cypher it?

"There are two aspects to Dir that make it easy to loop through files in a directory. First, the argument can contain wildcard characters (* and ?) so you can find similar files. Second, calling Dir without an argument finds the next file that matches the argument. Note that you must supply an argument the first time you call Dir.

This example loops through all the xls files in the current directory. It benignly prints their names to the Immediate Window, but you can modify it to open the files (with a Workbooks.Open call) or do just about anything you want.

Sub LoopThroughXLS()
Dim sFName As String
sFName = Dir(”*.xls”)
Do While Len(sFName) > 0
Debug.Print sFName
sFName = Dir
Loop
End Sub


This seems like part of what I want. I suppose I need to add the directory path to the folder containing the files: C:/UA/Files/Ascii somewhere? I of course want to take the contents of all the spreasheets and put them in a Table. Can you help me with the command that will take the contents of the spreadsheets found from the DIR loop? Thanks for your help.

 

[Guus2005]

You've got i all figured out! Nothing much to add.
You can inmport the spreadhseet using the Transferspreadsheet command

Sub LoopThroughXLS()
  Dim sFName As String
  sFName = Dir(”*.xls”)
  Do While Len(sFName) > 0
    Debug.Print sFName
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tmpYourInputTableName", sFName, True, "Sheetname!A1:C99"
    sFName = Dir
  Loop
End Sub

You can use the fieldnames in the spreadsheet "True" or not "False". If you want to select a part of a certain sheet use "Sheetname!A1:C99"

Enjoy!

 

[TylerTand]

Quote: "You've got i all figured out! Nothing much to add.
You can inmport the spreadhseet using the Transferspreadsheet command"

O.K I have been told that you can use the Transferspreasheet command to do this but I can't seem to find where the command is. I created a form and put a button on it so that I can perform the download and update. I right clicked on the button and went to build event. I have the code that you and I both found but now I need to tweak this to apply to my directory so that it knows where to look for the spreadsheets. Can you explain the parts of the code? I know this is asking alot but I don't know how to change it to fit my problem without understanding the code better. I am trying to learn but could use some more help.

Code:
Sub LoopThroughXLS() - (I take this to mean that it looks at all the files with an excel extention in the directory.)
Dim sFName As String -(I don' know what this means at all)
sFName = Dir(”*.xls”) - (what is a sFName?) spreadsheet Form Name?
Do While Len(sFName) > 0
Debug.Print sFName
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tmpYourInputTableName", sFName, True, "Sheetname!A1:C99"
sFName = Dir
Loop
End SubYou can use the fieldnames in the spreadsheet "True" or not "False". If you want to select a part of a certain sheet use "Sheetname!A1:C99"

-OK most of that was greek. the path to the folder with all the spreadsheets in it is c:/ua/files/ascii. I am assuming that this has to be part of the code so it know where to go to look for the excel files. By the way they are in Ascii format if that makes any difference. Thanks for your help.

 

[Guus2005]

Most of the code in my last post was yours. I understand now that you don't know how it works. Funny you picked the right code...

Sub LoopThroughXLS()
  Dim sFName As String [COLOR="SeaGreen"]'Dimensioning sFName, To store the filename. It is just a name. sPietjePuk would also be a valid name[/COLOR]
  sFName = Dir(”*.xls”) [COLOR="seagreen"]'Get the first spreadsheet in the current directory
 ' You need to extend this path to match yours. "c:/ua/files/ascii/*.xls"
 ' When sFName is empty there is no spreadsheet found or the end was reached[/COLOR]  Do While Len(sFName) > 0 [COLOR="seagreen"]'While one is found[/COLOR]
    Debug.Print sFName        [COLOR="seagreen"]'Echo the name to the immediate window. For debugging purposes.[/COLOR]
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tmpYourInputTableName", sFName, True, "Sheetname!A1:C99"
    sFName = Dir [COLOR="seagreen"]'Get the next spreadsheet[/COLOR]
  Loop
End Sub

Most texts are in Ascii. Ascii = American Standard Code for Information Interchange. It is a 7 bit code to connect a number to a readable character. See wikipedia. But if the spreadsheets are in ascii they are not actual spreadsheets but comma separated files (CSV) or otherwise?

HTH