Error importing series of Excel files

[Jim]

I have a command button on a form with code from Ken Snell's site to import
Excel workbook into Access. I have multiple workbook to import, and each
file has a related text box on the form. In vba, I import the first workbook,
then move the focus to the first text box. Each text box has code behind the
"On got focus" event to import the related workbook. All of the workbooks
are imported, but at the very end I get an error message that Access can't
move the focus to the last text box.
I tried moving the focus to another unrelated field, but I still get the
error. Any ideas what I'm doing wrong?

Thanks,
Jim

 

[Ken Snell MVP]

Sounds like you're using VBA code to change the focus? Post the VBA code
that you're using and let's see... probably a logic error.

 

[Jim]

Yes, I actually started with code I found on your site (thank you!!), but
added to it and I'm sure that's where the problem is. The code below is in
the On Click Event of a button on a form. I added a line of code just before
End Sub to move the focus to the next text box. It works in that they all
import, but I get an error saying it can't move the focus to the last text
box.


Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPathFile As String
Dim strPassword As String

On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objExcel = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

blnHasFieldNames = True

strPathFile = "C:\Documents and Settings\mjs475\Desktop\Templates"

strPassword = "vbNullString"

blnReadOnly = True

Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , blnReadOnly, , _
strPassword)
For lngCount = 1 To objWorkbook.Worksheets.Count
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Next lngCount

objWorkbook.Close False
Set objWorkbook = Nothing
If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing

For lngCount = colWorksheets.Count To 1 Step -1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tbl" & colWorksheets(lngCount), strPathFile, blnHasFieldNames, _
colWorksheets(lngCount) & "$"
Next lngCount

Set colWorksheets = Nothing

DoCmd.GoToControl "AccessSE"

'I have similar code in the On Got Focus Event of the control named
AccessSE, and so on until all workbooks are imported.

End Sub

Thanks for taking the time to look at this - I really appreciate it!

Jim

 

[Ken Snell MVP]

Most likely, then, the last control with file name that gets focus has code
that is trying to set focus to an invalid control or something.

But quite honestly, using code to change focus and then use GotFocus event
to run individual code procedures is not what I'd do in your situation. If
you want to have controls on the form with file names, then I'd use a single
VBA procedure to loop through all those textboxes and do something for the
filename in each control.

This allows you full control over the process and doesn't depend upon other
events to occur.

So, I'd use a command button to run the one event procedure (click event)
and use the one code segment that you posted, but wrap it with a loop that
reads from a control and sets a variable to the filename from that control,
and use that variable in the code. I can assist you with setting this up if
you can identify the control names that you're using. Hint: for this type of
setup, naming the controls in a 'sequential' way (e.g., txtFileName01,
txtFileName02, etc.) makes the looping code even easier.


A few other things I noted in your posted code:

strPathFile = "C:\Documents and Settings\mjs475\Desktop\Templates"
The above code step doesn't include a filename.
Because I don't see any place in the code where
you concatenate a filename to this string, I don't
understand how you're telling the code the specific
file to be read in this step:
Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , blnReadOnly, , _
strPassword)


strPassword = "vbNullString"
The above code step should be this, assuming that
there is no password on the file:
strPassword = vbNullString
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Jim]

I agree, looping code sounds like what I need, but I'm not familiar with how
to add it and really appreciate your help. I've got the other items you
mentioned cared for.

My text boxes are sequential and go from 1 to 22.
txtQCT01, txtQCT02, etc.

Thank you again!
Jim

 

[Ken Snell MVP]

OK using the code you posted originally, with slight tweaks and additions --
this code would be run from Click event of a command button:


Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long, lngControlLoop As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPathFile As String
Dim strPassword As String

On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objExcel = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

blnHasFieldNames = True

For lngControlLoop = 1 To 22

If Len(Me.Controls("txtQCT" & Format(lngControlLoop, "00")).Value & "") > 0
Then

strPathFile = "C:\Documents and Settings\mjs475\Desktop\Templates" & _
Me.Controls("txtQCT" & Format(lngControlLoop, "00")).Value

strPassword = "vbNullString"

blnReadOnly = True

Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , blnReadOnly, , _
strPassword)
For lngCount = 1 To objWorkbook.Worksheets.Count
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Next lngCount

objWorkbook.Close False
Set objWorkbook = Nothing


For lngCount = colWorksheets.Count To 1 Step -1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tbl" & colWorksheets(lngCount), strPathFile, blnHasFieldNames,
_
colWorksheets(lngCount) & "$"
Next lngCount

Set colWorksheets = Nothing

End If
Next lngControlLoop

If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing

End Sub

 

[Jim]

This works perfectly, thank you so much for taking the time to help!!

 

[Jim]

Ken, I'm using the code you provided successfully to import all 22
spreadsheets at one time. I'd like to give the users the option of selecting
specific spreadsheets to be imported. I have a second form with option
groups (not sure if that's the way to go, but I liked the look of the radio
buttons) for each of the spreadsheets. My goal is to have the user select
the spreadsheets they want to import, then click a command button to import
them. The value of the option group is the spreadsheet file name, and the
name is OptQCT01, 02, 03, etc. I'm hoping the same code can be tweaked to
accomplish this, but may be way off. If you have the time, I'd really
appreciate hearing from you again.

Thank you!
Jim

 

[Ken Snell MVP]

I'm not understanding the option button / group setup that you're using.
I'll need to know more specific information about what you have. For
example, are you talking about a separate option group for each worksheet?
Or one option group for all the spreadsheets together? What are the
"choices" of the option group (e.g., "Import", "Don't Import", or whatever)
and which button values correspond to each choice?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Jim]

I apologize if this posts twice, I got an error and not sure if my post was
saved.

Right now I have a separate option group for each workbook. I want the user
to "select" each workbook to be imported, then import the selected workbooks.
I thought I could use the radio button option group to "select", but looking
at your response I think a yes/no field would be better and code to import
if the value is true. I'd like to use the same type of looping code if
possible, but only import workbooks that are selected based on the value of a
checkbox or some other way to select it. I will use the same control name
pattern as in the first example since they're separate forms. Each workbook
will have a yes/no field named txtQCT01, txtQCt02, etc. I hope this makes
sense...

Thanks again,
Jim

 

[Ken Snell MVP]

OK, code changed -- although I'm not using "txtQCT##" for the Yes/No fields
because that is the same naming format that you had for the textboxes
containing the filenames, so instead I'm using txtQCT_YN##. The changed code
step is this one (to make it easier to find the change):

If Len(Me.Controls("txtQCT" & Format(lngControlLoop, "00")).Value _
& "") > 0 And Me.Controls("txtQCT_YN" & _
Format(lngControlLoop, "00").Value = True Then


The above code step used to be this:

If Len(Me.Controls("txtQCT" & Format(lngControlLoop, "00")).Value & "") > 0
Then




Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long, lngControlLoop As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPathFile As String
Dim strPassword As String

On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objExcel = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

blnHasFieldNames = True

For lngControlLoop = 1 To 22

If Len(Me.Controls("txtQCT" & Format(lngControlLoop, "00")).Value & "") > 0
_
And Me.Controls("txtQCT_YN" & Format(lngControlLoop, "00").Value _
= True Then

strPathFile = "C:\Documents and Settings\mjs475\Desktop\Templates" & _
Me.Controls("txtQCT" & Format(lngControlLoop, "00")).Value

strPassword = "vbNullString"

blnReadOnly = True

Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , blnReadOnly, , _
strPassword)
For lngCount = 1 To objWorkbook.Worksheets.Count
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Next lngCount

objWorkbook.Close False
Set objWorkbook = Nothing


For lngCount = colWorksheets.Count To 1 Step -1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tbl" & colWorksheets(lngCount), strPathFile, blnHasFieldNames,
_
colWorksheets(lngCount) & "$"
Next lngCount

Set colWorksheets = Nothing

End If
Next lngControlLoop

If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing

End Sub


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Jim]

I'm getting a run time error 424 object required. I added a close
parentheses after "True" in the new line of code but still getting the error.
Do you know what would cause that error?

 

[Ken Snell MVP]

Sorry, my error:

If Len(Me.Controls("txtQCT" & Format(lngControlLoop, "00")).Value _
& "") > 0 And Me.Controls("txtQCT_YN" & _
Format(lngControlLoop, "00")).Value = True Then

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Jim]

Thanks so much, once again works like a charm!
Jim

 

[Ken Snell MVP]

You're welcome. I should note that I "goofed" just a bit when I suggested
using "txtQCT_YN##" as the naming format for the checkboxes. While it's not
a problem for the form and the code to use the "txt" format, typically one
would prefix the control with letters that indicate what type of control, so
in this case it would have been better for me to suggest "chkQCT##" as the
naming convention. I think I was asleep at the switch a bit when I posted my
reply initially, so I wanted to provide this extra information "for the next
time"!

Good luck.

 

[Jim]

Thanks Ken. You know, I thought of that, but in my haste to try it out I left
them as txt. Thanks again for all your help!

 

[Jim]

Hoping to reach Ken Snell one more time...

I'd like to use similar code on another form to import multiple spreadsheets
based on the value of checkboxes, but in some cases, the spreadsheet the user
is asking to import doesn't exist yet and they see an error message. I need
to add error handling so it skips over that spreadsheet and goes on to the
next.

Here's my code:
-----------------------------------------------

Private Sub Command46_Click()

Select Case Yes
Case Me.chkQCT_YN01.value = Yes
DoCmd.OpenQuery "DSPOC_AccessMW", acNormal, acEdit
Case Me.chkQCT_YN02.value = Yes
DoCmd.OpenQuery "DSPOC_AccessSE", acNormal, acEdit
Case Me.chkQCT_YN03.value = Yes
DoCmd.OpenQuery "DSPOC_AccessSW", acNormal, acEdit
Case Me.chkQCT_YN04.value = Yes
DoCmd.OpenQuery "DSPOC_AccessW", acNormal, acEdit
End Select

Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long, lngControlLoop As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPathFile As String
Dim strPassword As String

On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objExcel = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

blnHasFieldNames = True

For lngControlLoop = 1 To 24

If Len(Me.Controls("txtQCT" & Format(lngControlLoop, "00")).value & "") > 0
And _
Me.Controls("chkQCT_YN" & Format(lngControlLoop, "00")).value = True Then

strPathFile =
"https://spsf07.web.att.com/sites/NSRC/Shared Documents/QCT_Major_Outage_Templates/" & _
Me.Controls("txtQCT" & Format(lngControlLoop, "00")).value

strPassword = vbNullString

blnReadOnly = True

Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , blnReadOnly, , _
strPassword)
For lngCount = 1 To objWorkbook.Worksheets.Count
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Next lngCount

objWorkbook.Close False
Set objWorkbook = Nothing

For lngCount = colWorksheets.Count To 1 Step -1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl" &
colWorksheets(lngCount), strPathFile, blnHasFieldNames,
colWorksheets(lngCount) & "$"
Next lngCount

Set colWorksheets = Nothing

End If
Next lngControlLoop

If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing

DoCmd.OpenQuery "SPOC_Name_&_TourApp", acNormal, acEdit

MsgBox "SPOC data imported successfully!"

End Sub

 

[Ken Snell]

Likely, what you want to do can be done. But I'm not understanding your
question with respect to the code that you've posted. The posted code
doesn't appear to be dependent in any way on a worksheet name from the form?
Can you provide more details for what you are wanting to do?
--

Ken Snell
http://www.accessmvp.com/KDSnell/