Importing From Excel 3

[Delindan]

I would like to import a file from Excel using vba coding and have been reading posts trying to put something together. I try to run it nothing happens. I'm probably doing something super stupid but I'm just learning so there you go. Here's what I have so far:

Private Sub cmdImportTemplates_Click()

Dim oXLApp As Excel.Application
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
Dim RSSpecialist As Recordset
Dim strSpcTemplate As String
Dim strFolder As String
Dim WB As Workbook
Dim strFileName As String
Dim introw As Long
Dim strSpecialistName As String
Dim rUsed As rangeset
Dim dbs As Database

'to create a temporary table in acess with two fields, breeder and family
Set dbs = OpenDatabase("Copy of Breeder Entry.accdb")
dbs.Execute "CREATE TABLE Temporary" _
& "(Breeder CHAR, Family CHAR);"
dbs.Close

'directing name and location of excel file to import
strFolder = Trim(txtFolder)
If Right(strFolder, 1) <> "\" Then
strFolder = strFolder & "\"
End If
strFileName = Trim(textfilename)
strSpcTemplate = strFolder & strFileName

txtCurrProfile = Null
DoEvents

'define last row with data as this will differ
Set oXLBook = oXLApp.Workbooks.Open(strSpcTemplate)
rUsed = Intersect(Range("A:AR"), ActiveSheet.UsedRange)

introw = oXLSheet.UsedRange.Rows.Count
set strSpecialistName = strSpcTemplate(B:1)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, temporary, strSpcTemplate, True, "A3:K & introw"

Set oXLSheet = Nothing
oXLBook.Close SaveChanges:=False
Set oXLBook = Nothing
oXLApp.Quit
Set oXLApp = Nothing

MsgBox "Finished Importing Data", vbOKOnly

End Sub

Any input would be helpful! Thanks

 

[SkipVought]

hi,

You have not been consistent applying Excel Object like...

Dim WB As Excel.Workbook

[b]
'need to Set the Excel Application Object
Set oXLApp = CreateObject("Excel.Application")[/b]
'define last row with data as this will differ
Set oXLBook = oXLApp.Workbooks.Open(strSpcTemplate)
[b]
'where do you set the sheet???
Set rUsed = oXLApp.Intersect(WHAT_SHEET_OBJECT.Range("A:AR") ,oXLApp.ActiveSheet.UsedRange)

'where do you set the sheet???
introw = oXLSheet.UsedRange.Rows.Count[/b]

Row Counts ought to be declared as LONG as the exceed 32,767.

You have a lot of cleaning up to do.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Delindan]

Not too surprising. I have a fairly low understanding at this point so I end up copying from examples and then trying to understand what I copied. Still...I really want to learn and am not sure how else to do that but to jump in. I need some basic understanding of working with excel from the vba coding in access. In other words, when importing (or exporting) from excel what exactly do I need to define and why. I'm also not quite sure what some of the set lines do. I haven't found a good reference for this either online or hard copy so if you could direct me I would be forever grateful!

 

[SkipVought]

When defining any other application, you must first use either CreateObject or GetObject. Check the description and code example in help.

When assigning any Object to an object variable, you must use the Set statement. For instance, you declared

Dim rUsed As rangeset
'...
'...
'...
rUsed = Intersect(Range("A:AR"), ActiveSheet.UsedRange)

Therre is no such thing in the Excel Object Model as a rangeset object. There is a Range object, so since you are programming in the Access Application, any Excel Application Object needs the Excel Application as part of the declaration...

Dim rUsed As Excel.Range

Also, the Intersect object is an Excel object, and the Range object needs to refer to a Sheet Object nad the ActiveSheet object is an Excel object...

Set rUsed = oXLApp.Intersect(SomeSheetObject.Range("A:AR"), oXLApp.ActiveSheet.UsedRange)

I generally avoid using ActiveSheet or ActiveANYTHING. Rather use specific object references.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Delindan]

This goes a loooong way in explaining some things. Thanks! So let me ask...if you're trying to figure out what the last used line on an excel spreadsheet is how would you do that without using activesheet? One additional question...the spreadsheets that I will be importing are single sheet. Is there any reason I should be defining an excel workbook or does worksheet suffice?

 

[SkipVought]

An Excel sheet does not exist without a workbook.

I always have one table per sheet -- first row in row 1 containing headings, first column in column A, table is contiguous

Here's what I would do...

'define last row with data as this will differ
dim lFirstRow as long, lLastRow as long
With  oXLApp.Workbooks.Open(strSpcTemplate)
   With .Sheets(1)
      lFirstRow = .row
      lLastRow = .row + .rows.count - 1
   End with
End with

But I hardly ever do that. I'm usually looping thru data values in column A, for instance...

dim r as Excel.range
With  oXLApp.Workbooks.Open(strSpcTemplate)
   With .Sheets(1)
      for each r in .Range(.cells(2,1), .cells(2,1).end(xldown))
         
      next
   End with
End with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Delindan]

thank you...I'll give all of this a try

 

[SkipVought]

for a broader view and better information for Excel VBA, browse & post in forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Delindan]

Sigh...maybe I'm making this too complicated. I didn't completely understand your range example but stuck it it there and it's getting an error (surprise!) I'm guessing I don't have the set commands that I need. It gets down to the docmd transferspreadsheet and says I don't have the file Temporary defined which is what I thought I did when I created it. Here's what I have:

Option Compare Database
Option Explicit

Private Sub cmdimport_Click()

Dim xlApp As New Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet
Dim strSpcTemplate As String
Dim strFolder As String
Dim strFileName As String
Dim introw As Long
Dim strSpecialistName As String
Dim dbs As Database
Dim r As Excel.Range

' here I'm trying to open my database and create a table named temporary to receive imported records
Set dbs = OpenDatabase("Copy of Specialist Entries Table")
dbs.Execute "CREATE TABLE Temporary"

'defining name and location of file
strFolder = Trim(txtFolder)
If Right(strFolder, 1) <> "\" Then
strFolder = strFolder & "\"
End If
strFileName = Trim(txtfilename)
strSpcTemplate = strFolder & strFileName

'opening the excel file
With xlApp.Workbooks.Open(strSpcTemplate)
With .Sheets(1)
For Each r In .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
'getting stuck on the Temporary ...says object not defined. Is the transfer spreadsheet an Excel command?
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, Temporary, strSpcTemplate, True
Next
End With
End With
dbs.Close


MsgBox "Imported Successfully"

xlApp.Quit

Set xlApp = Nothing
Set XLBook = Nothing

End Sub

 

[Delindan]

I'm guessing you'll be rather shocked but I got the import to work. Now I would like to append from my temporary table to the real database. For the most part this is a straight append so I created an append query and copied the vba syntax. However this isn't all there is to it. The way the spreadsheet is set up, the FS Specialist is in cell 1,2 on the spreadsheet with the data titles on the line below and data below that. So I import a3:k100 and set up a temporary variable to hold the specialist name. I am trying to do the append as they had it in addition to adding the specialist name to each of the records I am appending. (I hope I explained that clearly sorry if not) The temporary variable is call strSpecialistName. It is getting hung on the syntax of that import. Do you see anything obvious? Thanks!

Option Compare Database
Option Explicit

Private Sub cmdimport_Click()

Dim xlApp As New Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet
Dim strSpcTemplate As String
Dim strFolder As String
Dim strFileName As String
Dim introw As Long
Dim strSpecialistName As String
Dim dbs As Database
Dim r As Excel.Range


strFolder = Trim(txtFolder)
If Right(strFolder, 1) <> "\" Then
strFolder = strFolder & "\"
End If
strFileName = Trim(txtfilename)
strSpcTemplate = strFolder & strFileName

With xlApp
.Visible = False
Set XLBook = .Workbooks.Open(strSpcTemplate)
End With
strSpecialistName = xlApp.Worksheets(1).Cells(2, 1)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Temporary", strSpcTemplate, False, "a3:k100"

xlApp.Quit
Set xlApp = Nothing
Set XLBook = Nothing

INSERT INTO [Crop Data] ([FS Specialist], Breeder, Family, Crop, [Sub Crop], [Data Year], [advcd phs 4], [advcd phs 5 advcd comm], [advcd phs 5 entered FS at phase 3], [advcd phs 5 entered FS at phase 4], [moved phs 4 to phs 6], [Estimate of new entries] )
SELECT strSpecialistName, Temporary.F1, Temporary.F2, Temporary.F3, Temporary.F4, Temporary.F5, Temporary.F6, Temporary.F7, Temporary.F8, Temporary.F9, Temporary.F10, Temporary.F11
FROM [Crop Data], [Temporary]


'MsgBox "Imported Successfully"



End Sub

 

[PHV]

VBA can't execute straight SQL code.

Dim strSQL As String
strSQL = "INSERT INTO [Crop Data] ([FS Specialist],Breeder,Family,Crop,[Sub Crop],[Data Year],[advcd phs 4]" _
 & ",[advcd phs 5 advcd comm],[advcd phs 5 entered FS at phase 3],[advcd phs 5 entered FS at phase 4]" _
 & ",[moved phs 4 to phs 6],[Estimate of new entries])"
strSQL = strSQL & "SELECT '" & strSpecialistName & "',F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11 FROM Temporary"
DoCmd.RunSQL strSQL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

To add to PHV's suggestion, you may want to turn off warning messages when running the SQL, if this is something run on a regular basis. You do that with DoCmd.SetWarnings. So in this case, it'd be:

Dim strSQL As String
strSQL = "INSERT INTO [Crop Data] ([FS Specialist],Breeder,Family,Crop,[Sub Crop],[Data Year],[advcd phs 4]" _
 & ",[advcd phs 5 advcd comm],[advcd phs 5 entered FS at phase 3],[advcd phs 5 entered FS at phase 4]" _
 & ",[moved phs 4 to phs 6],[Estimate of new entries])"
strSQL = strSQL & "SELECT '" & strSpecialistName & "',F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11 FROM Temporary"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Also, are you really importing a bunch of fields as F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11? Or did the import just add those on? I have had that happen on occasion when quickly importing a spreadsheet without deleting rows and columns without any data.

To delete empty rows from your import, you could run the following SQL string, or put it in a query and run that.

In the example I give here, I'm assuming that there will always be a Specialist value for every record. If not, then just go with a field (usually an ID field is best - not an auto-generated ID field by Access, though) that would always contain data:

DELETE t.* FROM MyTable t WHERE t.[B]Specialist[/B] Is Null[/CODD]

Of course, if you run that in VBA, you'll need to put it in a String variable and run it same as PHV's example, or just run it inside of quotes after the DoCmd.RunSQL command.

 

[Delindan]

This is great...I'm so grateful for all the help. I'm hoping this will lead to future successes without having to ask so much!!

 

[kjv1611]

Well, the problem isn't in asking. The problem is fighting the natural tendency to just copy other work, and "git'r'dun" "just this time".. and then that progresses to every time... and then one day you're stuck without access to ask the questions, and you can't figure it out on your own.

So for instance, oftentimes, I know that PHV and others will say, "go check this out" without giving a full solution. That way, since you have to do some digging on your own, when you come into something else new, you'll have some building blocks to go on, and you can more likely solve it on your own.

So by all means don't fret about asking questions. Just don't let yourself get into a habit of wanting to get the full solution from others - rather try to do it on your own... then post what you've done.. what is working, what isn't working, and let the others critique and add-to your work. You'll learn a TON that way.

 

[kjv1611]

doggoneit! I got mixed up in 2 differnet threads... when you said about "asking questions"... all of that didn't really pertain here.. you DID share your work.

 

[Delindan]

Actually it's great advice although harder to push yourself to do it. I'm still a little unclear when going back and forth between excel and access but have learned a ton. Thanks again!