Paste appending from Excel file into Access table (1 Viewer)

[Bee*]

Hello, been working on automating some formatting in an excel document via an access form. My code so far is thus:

Private Sub runFiles(fileName As String, fileMonth As String)

Dim xlApp As Object
Dim xlBook As Object

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Open "N:\Premium Research\All Data\" & fileMonth & " " & fileName & ".xls", True, False
Set xlBook = xlApp.Application.ActiveWorkBook

With xlBook.Worksheets("Premiums")
.blah blah lots of formatting code works fine
End With
xlBook.Close SaveChanges:=True
xlApp.Quit
End Sub

So as you can see this just opens the excel file specified and cuts a few rows, adds names and so forth, this runs no problem. My question is, is there a way to automate the pasting of this data set into an access table, including title rows? There are a few files and each one has a different amount of columns so I can't just specify a range of cells to copy and paste.

Any kind of help appreciated. Thanks,

Adam.

 

[FireStrike]

Hello,

Have you tried something like this?

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblFromExcel", strPath

acImport tells it to import
acSpreadsheetTypeExcel9 tell it what format your excel file is in
"tblFromExcel" is the name of the table you want to put it in
strPath is the path and filename of the Excel file.

 

[steveboydon]

If you want to be fancy you could also read the spreadsheet line by line
Dim rst1 As New ADODB.Recordset
Dim strsql As String
dim i as integer

strsql = " select * from tblname"

rst1.Open strsql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

i = 1 (or the row where the data starts)

do while not cells(i,1) = ""
with rst1

.addnew
!firstfield = cells(i,1) first column
etc etc
.update

end with

i = i + 1
loop

rst1.Close


then kill all the excel refernces

You will also need to set a reference to ado.

Of course you can do similar with DAO, I like ADO as it is portable if you need to upsize,


Steve

 

[Bee*]

Hello,

Have you tried something like this?

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblFromExcel", strPath

acImport tells it to import
acSpreadsheetTypeExcel9 tell it what format your excel file is in
"tblFromExcel" is the name of the table you want to put it in
strPath is the path and filename of the Excel file.

That is SO close, the only problem is the titles in Row A are counted as data and therefore get imported as such, and then the import process complains that I don't have a column called F1, F2 etc. Is there an option to get it to count the title rows as titles?

 

[ghudson]

Set the HasFieldNames option to True

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblFromExcel", strPath, [COLOR="Red"][B]True[/B][/COLOR]