TransferSpreadsheet Code problem ! (1 Viewer)

[Duracell]

Hi,

I need to import data from a specific Excel worksheet (RCO Survey) into an existing table (RCO Data) but I want the user to be able to define the range of the worksheet that is imported. The also determine where the spreadsheet is (e.g. which directory on their own PC)

I am not too good with VBA so I have tried to do the import with the code below but it does not import any data.

Any guidance or suggestions is appreciated.




Private Sub Command41_Click()
On Error GoTo Err_Command41_Click

'This section request the filename and the ranges in the spreadsheet tabs to be imported
Title = "Import Data"


Continue = MsgBox("You will need to know the Excel range that you want to import for the RCO Data before you proceed, You can CANCEL the import at the next step", , [Title])

If Continue = "" Then
GoTo Err_Command41_Click
End If

pathname = InputBox("Enter the full path and file name of the Site Survey Excel File, Data will be imported from the RCO Survey tab (Don't forget the .xls extension!)", [Title])

If pathname = "" Then
GoTo Err_Command41_Click
End If

RCSurveyData = InputBox("Enter the Excel Range for the RCO Survey TAB to be imported, e.g. this should be in the format A4:AE450", [Title])
If RCSurveyData = "" Then
GoTo Err_Command41_Click
End If

'Imports Main Data
DoCmd.TransferSpreadsheet acImport, 8, "RCO Data", pathname, False, "RCO Survey!" & RCSurveyData


Exit_Command41_Click:
Exit Sub

Err_Command41_Click:
MsgBox "Data has NOT been Imported" 'Err.Description
Exit Sub

End Sub

 

[Beetle]

You don't say if you're getting any error message or not, so it's hard to offer anything as far as what the specific problem might be, but here are some observations.

1) You should explicitly declare your variables (like RCSurveyData, etc.)

2) You should use Option Explicit in your code modules (this should go right under Option Compare Database at the top of the module). This will prevent undeclared variable issues, which may be the cause of your current problem.

Option Compare Database
Option Explicit  [COLOR=red]'<<< Add this at the top of your module[/COLOR]

3) I would not rely on users to manually enter a full path to a file. It's more user friendly, and more reliable, to give them a File Dialog so thay can navigate to, and select, the appropriate file.

The example code below opens a File Dialog window so the user can select the appropriate file (in this example the File Dialog is limited to files with the ".xls" extension, but that could easily be modified), then pops an input box so they can enter the desired Range, then imports the selected Range into the RCO Data table.

Private Sub cmdImport_Click()
On Error GoTo HandleError
 
Dim fSearch As FileDialog  [COLOR=red]'<<<
[/COLOR]Dim strPath As String       [COLOR=red]'<<< These are the variable declarations[/COLOR]
Dim strRange As String     [COLOR=red]'<<<[/COLOR]
Dim varFile As Variant      [COLOR=red]'<<<[/COLOR]
 
Set fSearch = Application.FileDialog(msoFileDialogFilePicker)
 
With fSearch
    .title = "Search for the Excel file"
    .filters.Clear
    .filters.Add "Excel Files", "*.xls"
    If .Show = True Then
        For Each varFile In .SelectedItems
            strPath = varFile
        Next
    Else
        MsgBox "Operation Cancelled."
    End If
End With
 
If strPath <> "" Then
    strRange = InputBox("Please enter a Range in the format A4:AE450", "Enter Worksheet Range")
    If strRange = "" Then
        MsgBox "No Range was entered.", , "Operation Cancelled"
    Else
        DoCmd.TransferSpreadsheet , 8, "RCO Data", strPath, , "RCO Survey!" & strRange
    End If
End If
 
Exit_here:
    Exit Sub
    
HandleError:
    If Err.Number = 3011 Then
        MsgBox "Please enter a valid Range.", , "Operation Failed"
    Else
        MsgBox Err.Number & " " & Err.Description
    End If
    Resume Exit_here

End Sub

 

[Duracell]

Thanks for this, I will give it a try.

for reference, the error message that I get is 'data has NOT been imported" i.e. my error message at the bottom of my code.

Thanks again for the steer.

 

[Duracell]

HI Sean,

I tried the code that you suggested (included the Option Explicit statement) but when I run the code I get an error message saying:

'Compile Error: User-defined type not defined '

with the ' Dim fSearch As FileDialog ' statement highlighted. Do I need to define this somewhere, if so can you tell me where I should do it.

Many thanks

 

[JANR]

FileDialog requires refrence to Microsoft Office.

In the codewindow select:

Tools -> Refrences -> Microsoft Office v??

Or you can LateBind the refrence:

Private Sub cmdImport_Click()
On Error GoTo HandleError
 
Dim fSearch As [COLOR=red]Object[/COLOR]  [COLOR=red]'<<<
[/COLOR]Dim strPath As String       [COLOR=red]'<<< These are the variable declarations[/COLOR]
Dim strRange As String     [COLOR=red]'<<<[/COLOR]
Dim varFile As Variant      [COLOR=red]'<<<[/COLOR]

[COLOR=red]Const [/COLOR][COLOR=#000000][COLOR=red]msoFileDialogFilePicker = 3[/COLOR][/COLOR]
[COLOR=#000000][COLOR=black]...[/COLOR]

[/COLOR]

JR

 

[Duracell]

Brilliant, Thanks JR, that solved the problem and I can now import the data.

I have one last problem (I think !)

The existing table (RCO Data) that I am importing into has named fields e.g. DDI, Extension, FirstName etc.

When I do the import I get an error message saying " Field F1 does not exist in destination table 'RCO Data' ". I have tried adding 'True' and 'False' in the statement:

DoCmd.TransferSpreadsheet , 8, "RCO Data", strPath, , "RCO Survey!" & strRange

e.g.

DoCmd.TransferSpreadsheet , 8, "RCO Data", strPath, False , "RCO Survey!" & strRange

But this does not make any difference. If I delete the table RCO Data and run the import it works fine but sets all of the field names to F1, F2, F3 etc.

Basically, what I want to do is import just the data from the spreadsheet (which has headings) into the existing table which already has associated field names.

I think that I am very close but I have tried everything that I can think of to make it fully automated. Worst case I could make a copy of the existing table and rename it, delete the RCO Data table, do the import (to the new RCO Data table) copy the data to the table copy and then rename the table back to RCO Data. Not ideal but it I could probably manage like this.

Cheers

 

[amrizvi]

Set has field TRUE

DoCmd.TransferSpreadsheet , 8, "RCO Data", strPath, TRUE , "RCO Survey!" & strRange

There will be an error if in next import, worksheet has some additional columns
therefore, before each import delete the RCO Data

DoCmd.DeleteObject acTable, "RCO Data"
DoCmd.TransferSpreadsheet , 8, "RCO Data", strPath, TRUE , "RCO Survey!" & strRange

Once again there will be an error If RCO Data is not yet created or deleted manually so handle it via On Error Resume Next

On Error Resume Next
DoCmd.DeleteObject acTable, "RCO Data"
DoCmd.TransferSpreadsheet , 8, "RCO Data", strPath, TRUE , "RCO Survey!" & strRange