Importing an excel spreadsheet into and existing Access tbl

[amypaulsen]

I'm trying to pull in data from excel and dump it into a table within Access that is linked to queries. I'll need to do this once a month so I need to have it pull into the appropriate Table. I tried an append query and cant get it to work.

HELP!

 

[amypaulsen]

Re: Importing an excel spreadsheet into and existing Access

the only problem is that I need to update every single line in each table every month (approx 800 lines). So the tables start off with one line of blank data and I'm trying to update them through an append query...but if I over-ride the exising table name ex: Period01, it doesn't recognize the table anymore. I don't know how to change every line in the table while not having to change any named references in the query.

 

[Joe4]

Re: Importing an excel spreadsheet into and existing Access

the only problem is that I need to update every single line in each table every month (approx 800 lines). So the tables start off with one line of blank data

I am not sure why you are doing this. How do blank lines of data get in the table? Why are they being imported before they are being filled out? I think you would want to import each row of data with a date field attached to it at the time you need it. Then just keeping adding to your table (importing to an existing table will noe overwrite the data unless you are importing the same table (i.e. make sure the key fields are different each time by using something like date fields)).

Also, you would NOT use an Append Query to update existing rows in a table. An Append Query adds new rows to a table. If you want to update existing rows of data, you would use an Update Query instead.

It sounds like some of the issues you have involve the structure of your database. There are numerous rules of normalization for designing a database that you may want to check out (do a search on Normalization). A poorly designed database is very difficult to work with.

 

[amypaulsen]

Re: Importing an excel spreadsheet into and existing Access

i need to be able to run each week at any given time throughout the year, therefore I must house ALL of the data, prelim as well as finals, and not replace any data.

I'm looking into Macros now but my book says to use VB which I am familiar with from using Excel anyway. I just need to learn the different name properties.

 

[amypaulsen]

Re: Importing an excel spreadsheet into and existing Access

here is the code I am using in Excel. This applies only to current week information. I will need to add additional code that will retrieve the information based upon the week date entered, if there has been a period close, then I need to pull that information into the PTD, QTD & YTD sheets. This is what I am trying to do now. If the current date is after the period close, I will need to select the PD close numbers as well as the intermitten weeks to accomodate QTD & YTD which is why I need every week.

Option Explicit

Sub FetchAllData(WeekNum As String)
FetchData "qrytransactions03", WeekNum, 20
FetchData "qrysdxlaborhrs03", WeekNum, 26
FetchData "qryVendorHrs03", WeekNum, 31
FetchData "qrySalesRevenues03", WeekNum, 57
FetchData "qryProductCost03", WeekNum, 62
FetchData "qryTotLaborCost03", WeekNum, 80
FetchData "qryControllables03", WeekNum, 85
FetchData "qryNonControl03", WeekNum, 90
FetchData "qryBgtTransactions04, weeknum, 16"
FetchData "qryBgtSdxLaborHrs04", WeekNum, 24
FetchData "qryBgtVendorHrs04", WeekNum, 29
FetchData "qryBgtSalesRevenues04", WeekNum, 55
FetchData "qryBgtProductCost04", WeekNum, 60
FetchData "qryBgtTotLaborCost04", WeekNum, 78
FetchData "qryBgtControllables04", WeekNum, 83
FetchData "qryBgtNonControl04", WeekNum, 87



End Sub
Sub FetchData(QueryName As String, WeekNum As String, RowNum As Integer)
Dim objAccess As Object
Dim objDB As Object
Dim rst As Object
Dim strSQL As String
Dim colnum As Integer
Dim foundit As Boolean



Sheets("Current Week").Select

'Opens Access

Set objAccess = CreateObject("access.application")

With objAccess
.opencurrentdatabase (ActiveWorkbook.Path & "\Flash FY05.mdb")

BeginLoop:

Set objDB = objAccess.currentdb()
With objDB

strSQL = "SELECT " & QueryName & ".UNIT, " & QueryName & ".[" & WeekNum & "] from " & QueryName & ""
Set rst = objDB.openrecordset(strSQL)

If Not rst.RecordCount = 0 Then

rst.movefirst

Sheets("current week").Select
Do

colnum = 1
foundit = False
' For Each fld In rst.fields

Do
If Cells(12, colnum).Value <> rst.fields("UNIT").Value Then
colnum = colnum + 1
Else
foundit = True
End If
Loop Until colnum > 223 Or foundit



If Cells(12, colnum).Value = rst.fields("UNIT").Value Then
Cells(RowNum, colnum).Value = rst.fields(WeekNum).Value
End If

colnum = colnum + 1

' Next fld


rst.movenext
Loop Until rst.EOF
End If

End With
End With

rst.Close

objAccess.Application.Quit
End Sub

I know it sounds crazy but there is a method to the madness

 

[Joe4]

Re: Importing an excel spreadsheet into and existing Access

I know Excel much better than Access, and I know a lot of Excel VBA, but I found it difficult to write Access VBA at first without some assistance from books and other sources. It is similar, but different enough to keep it from being easy (especially when you get into writing SQL).

One thing to note: Unlike Excel, Access does not have a macro recorder. However, one thing that I have found particularly useful is that you can convert macros to VBA code.

So, if you need to do something with importing/exporting spreadsheets, you can set up a very basic example in a Macro, then highlight the Macro, go to Tools | Macro | Convert Macros to Visual Basic. This often gives you a good starting point.

 

[amypaulsen]

Re: Importing an excel spreadsheet into and existing Access

thanks, that is exactly what I am trying to do now. I'm reading the Macro chapter ( I have the "Inside/Out MS Access 2003" book) and then I'll move on to the VBA chapter. I am the same way and am MUCH more comfortable with Excel, I recognize the similarities although the differences in verbiage are significant. I'm sure I will stumble through this and eventually be an Access expert as well...hee haw (yeah right!). Thanks for your help. This whole thing is crazy although I will do as requested...

 

[amypaulsen]

Re: Importing an excel spreadsheet into and existing Access

how do you convert macros into VBA code?

 

[Joe4]

Re: Importing an excel spreadsheet into and existing Access

Quote from previous post:

So, if you need to do something with importing/exporting spreadsheets, you can set up a very basic example in a Macro, then highlight the Macro, go to Tools | Macro | Convert Macros to Visual Basic. This often gives you a good starting point.

Keep in mind that this referring to Access Macros to Access VBA (not Excel VBA or stand-alone VB).

If start a new Macro is Access, select the "Transfer Spreadsheet" action. A little table/grid will appear in the lower left corner where you enter in the Type (Import/Export), Table Name, File Name, etc. Enter these values, save the Macro and exit. Then right click on the macro to select it and follow the steps I quoted above. The resulting code can be found under Modules.