MS VBA DoCMD Transferspreadsheet - Use form data to choose excel sheet - HELP 1

[debq]

I am trying to get the following code to use the sheet name that I choose on a list box in a DoCMD. TransferSpreadsheet path. The code works if I place the actual name in the path. But when I try to get it to read the cboText choice I get an error that it cannot find the object.Can someone please help me??? I am not very good with VBA.

Here is my code.

Option Compare Database

Sub ImportExcel()
Dim Filepath As String
Dim cboText As String

cboText = [Forms]![frmUpdateCMIData]![cboParentLocation]

Filepath = "C:\Users\p418549\Desktop\CA CMI Data 2014-12-30 by facility tabs.xlsx"
If FileExist(Filepath) Then

DoCmd.TransferSpreadsheet acImport, , "ImportFromExcel", Filepath, False, "cboText!E9:P22"

Else
    MsgBox "File not found. Please check filename or file location."
End If
    MsgBox "File Transfer Successful"


  Dim StrSql As String
   Dim tblname As String
       tblname = "ImportFromExcel"
   StrSql = "ALTER TABLE [" & tblname & "] ADD COLUMN ID Counter"
   DoCmd.RunSQL StrSql
End Sub

Function FileExist(sTestFile As String) As Boolean
   'This function does not use DIR since it is possible that you might have
   'been in the middle of running DIR against another directory in
   'an attempt to match one directory against another.
   'It does not handle wildcard characters
   Dim lSize As Long
   On Error Resume Next
   'Preset length to -1 because files can be zero bytes in length
   lSize = -1
   'Get the length of the file
   lSize = FileLen(sTestFile)
   If lSize > -1 Then
      FileExist = True
   Else
      FileExist = False
   End If
End Function

 

[Andrzejek]

try:

... False, cboText & "!E9:P22"

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[debq]

Thank You!!! That worked perfectly!!

 

[Andrzejek]

I am glad, but do you know WHY this (yours)
[tt]False, "cboText!E922"[/tt] did not work, but this (mine)
[tt]False, cboText & "!E922"[/tt] worked OK?

I want you to understand it, so you can code it in the future.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[debq]

I think your code works because cboText is a String data type that is being concatenated or added to the string "!E922". In my code I had the two pieces together as one continuous string and the system could not find that full string as a sheet name and Range in the excel spreadsheet. Am I on the right path???

 

[Andrzejek]

BINGO !

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.