Type missmatch while XLS-import?

[Armitage2k]

Hi,

i run into a type missmatch error while I am trying to import data from a closed xls workbook (with success), but get the error while trying to replace content of a memo field in the newly imported table.

Sub GetDataFromClosedWorkbook()
Dim xlApplication As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlWorksheet As Excel.Worksheet
Dim db As Database, rec As recordset
Dim Filename As Object
Dim Selection, Category, Finished As String
' Pick a file to import
Set Filename = Application.FileDialog(msoFileDialogFilePicker)
    With Filename
    .InitialView = msoFileDialogViewDetails:
    .InitialFileName = "C:\":
    .Filters.Clear:
    .Filters.Add "Pick .xls File", "*.xls", 1:
    .ButtonName = "Import file":
    .Title = "Search for Database_Import.xls file"
    ' If no file is selected, close the sub, else keep going
        If .Show = -1 Then
            Selection = .SelectedItems(1)
        Else:
            Exit Sub
        End If
    End With
    ' Show messagebox to select which entries to import
    Category = InputBox("Which worksheet do you want to import?" & Chr(10) & _
                        "VIP - PCP - LSG - OCT - IHG", "Select Entries", "VIP")
Set xlApplication = CreateObject("Excel.Application")
Set xlWorkbook = xlApplication.Workbooks.Open(Selection, True, True)
Set xlWorksheet = xlWorkbook.Worksheets(Category) ' define which worksheet shall be imported
Set db = CurrentDb
Set rec = db.OpenRecordset(Category) ' define which table shall be opened
 
    ' clear selected table
    DoCmd.SetWarnings False
        DoCmd.RunSQL "DELETE " & Category & ".* FROM " & Category & ";"
    DoCmd.SetWarnings True
    ' open the source workbook, read only
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, Category, Selection, True
 
    ' close the source workbook without saving any changes
    xlWorkbook.Close False
 
    'replace * with: Linebreak + Space + * in column Notes
    [B][COLOR=red]StarUpdate_query = "Update Category![Notes] = Replace([Notes], " * ", Chr(10) & Chr(42) & Chr(32))"[/COLOR][/B]
    'StarUpdate_query = "UPDATE" & Category
    'StarUpdate_query = StarUpdate_query & "SET [Notes] = Replace([Notes], " * ", Chr(13) & Chr(10))"
    DoCmd.RunSQL StarUpdate_query
 
    ' show alert that process was sucessful
    Finished = MsgBox("Import of " & Category & " successfully finished", vbOKOnly, "Finished")
 
Set xlWorkbook = Nothing ' free memory
rec.Close
End Sub

Sure its just a minor thing, but I just cant get it to work.

Thanks for the support!
A2k

 

[John.Woody]

Try importing the data manually and looking at the Data Types in the table. Access tries to guess what type it should be, and it could be set to a number data type instead of text.

HTH

 

[Armitage2k]

so, HOW do I make access understand that I want everything, ie. treated as text type? How do I write that into the code? I dont know about such a reference....

Besides, I am not really sure it is the import, rather the replacement, as this error occurs in the above highlighted area which is dealing with that later.

Thanks,
A2k

 

[jal]

I don't understand your syntax. I always thought an UPDATE query was supposed to look something like this:

Update Category SET [Notes] = Replace([Notes], " * ", Chr(10) & Chr(42) & Chr(32) )


"Update Category SET [Notes] = Replace([Notes], ' * ', Chr(10) & Chr(42) & Chr(32) )"

And if the field is "Memo", I don't think it is a datatype problem because Memo is text. However, if the column were numeric, you could recognize it as text by using Cstr(Notes).

 

[Armitage2k]

well, I admit that I was experimenting with the syntax as I got an "Expecting Command End" error when using SET (see below)

Update Category SET [Notes] = Replace([Notes], " * ", Chr(10) & Chr(42) & Chr(32) )

As a result, I tried to pack this whole thing into a query, which was suggested by another user who had the same troubles as me. something like this:

StarUpdate_query = "Update Category![Notes] = Replace([Notes], " * ", Chr(10) & Chr(42) & Chr(32))
DoCmd.RunSQL StarUpdate_query

however, that syntax returned me the "Type missmatch" error.

Another user then mentioned that he had a similar problem which was caused because the string was too long, hence I cut the whole thing in 2 parts:

StarUpdate_query = "UPDATE" & Category
StarUpdate_query = StarUpdate_query & "SET [Notes] = Replace([Notes], " * ", Chr(13) & Chr(10))"
DoCmd.RunSQL StarUpdate_query

Same game, type missmatch error.

So in the end, I have 3 different syntax and none of them seems to work!

To make it clear what I actually want to do, as the main purpose actually seems to work fine:

I want to replace the "*" symbols, in the table which I have defined via variable "Category", located in column "Notes" with these characters "Chr(10) & Chr(42) & Chr(32))" (=> Linebreak + Star + Space). This should be done after the data import from the selected file (which is working fine).

I appreciate every help on this!
Thanks,

A2k

 

[jal]

DoCmd.RunSQL "Update Category SET [Notes] = Replace([Notes], ' * ', Chr(10) & Chr(42) & Chr(32) )"

 

[Armitage2k]

One step closer to success now, thanks

now i get the error that access cannot find the variable "Category", although it is defined as a string earlier on in the script. Category is defined in an inputbox and serves as value for the name of the worksheet to import and as the name of the table where the data is supposed to be imported.

Sub GetDataFromClosedWorkbook()
Dim xlApplication As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlWorksheet As Excel.Worksheet
Dim db As Database, rec As recordset
Dim Filename As Object
Dim Selection, Category, Finished As String

' Pick a file to import
Set Filename = Application.FileDialog(msoFileDialogFilePicker)
    With Filename
    .InitialView = msoFileDialogViewDetails:
    .InitialFileName = "C:\":
    .Filters.Clear:
    .Filters.Add "Pick .xls File", "*.xls", 1:
    .ButtonName = "Import file":
    .Title = "Search for Database_Import.xls file"

    ' If no file is selected, close the sub, else keep going
        If .Show = -1 Then
            Selection = .SelectedItems(1)
        Else:
            Exit Sub
        End If
    End With

    ' Show messagebox to select which entries to import
    Category = InputBox("Which worksheet do you want to import?" & Chr(10) & _
                        "VIP - PCP - LSG - OCT - IHG", "Select Entries", "VIP")

Set xlApplication = CreateObject("Excel.Application")
Set xlWorkbook = xlApplication.Workbooks.Open(Selection, True, True)
Set xlWorksheet = xlWorkbook.Worksheets(Category) ' define which worksheet shall be imported
Set db = CurrentDb
Set rec = db.OpenRecordset(Category) ' define which table shall be opened


    ' clear selected table
    DoCmd.SetWarnings False
        DoCmd.RunSQL "DELETE " & Category & ".* FROM " & Category & ";"
    DoCmd.SetWarnings True

    ' open the source workbook, read only
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, Category, Selection, True
        
    ' close the source workbook without saving any changes
    xlWorkbook.Close False
    
    'replace * with: Linebreak + Space + * in column Notes
    DoCmd.RunSQL "Update Category SET [Notes] = Replace([Notes], ' * ', Chr(10) & Chr(42) & Chr(32) )"
    
    ' show alert that process was sucessful
    Finished = MsgBox("Import of " & Category & " successfully finished", vbOKOnly, "Finished")
    
Set xlWorkbook = Nothing ' free memory
rec.Close

End Sub

ALMOST DONE!!! THANKS A LOT!!

A2k

 

[jal]

Oh, sorry, you'll probably need:

DoCmd.RunSQL "Update " & Category & " SET [Notes] = Replace([Notes], ' * ', Chr(10) & Chr(42) & Chr(32) )"

 

[Armitage2k]

great, its working now, thanks a lot!

I just have one more little problem. When importing the data, the field type of the "Notes" column, which is originally a Memo Field, is reseted to Text-Field, hence the whole replacement of the * symbol and line break insert is pointless, since a text-field cannot display line breaks.

now, how can let vba change the field type of a by variable defined table? Eg. the column name is always "Notes", just the table name is defined via "Category". Before letting vba do the replacement, i need to change the field type first.

thanks a lot for all the help here, it really helps me out lot.
THANKS

A2k

 

[jal]

A text-field cannot display line breaks.

I wasn't aware of that. As far as I know, a text field will preserve the line-break chars. For example if you export the data to a textfile, I'm guessing you would see the linebreaks. (I could be wrong). In what context are you finding otherwise?

Anyway you have a couple of options. One is to change the field, after the import, to a memo field.

DoCmd.RunSQL "alter table " & Category & " alter column [Notes] MEMO "


Another option is to create the Access dest table beforehand with Notes set to be a Memo field. Then, instead of using a SELECT INTO command (which destroys the prefabricated table), use the command that preserves the table (INSERT INTO). Like this:

DoCmd.RunSQL "INSERT INTO " & Category & " SELECT * FROM [Excel 8.0;DATABASE=" & SELECTION & ";HDR=Yes; IMEX=1].[" & Category & "$]"

In other words, don't use "TransferSpreadsheet."

HTH

 

[jal]

Wait a minute, doesn't TransferSpreadsheet preserve the table? If you set the field to be Memo, and then use TransferSpreadsheet, the "Memo" status should preserve.

 

[Armitage2k]

I tested with transferspreadsheet and it seems to delete the field status of the notes column, hence no preserving with this command.

However, I tried the

DoCmd.RunSQL "alter table " & Category & " alter column [Notes] MEMO"

solution but ended up with an error stating that access cannot block the table as it is already in use by another process (which should not be true since the import is done already at that step...), thus cannot complete the macro. I tried to escape that error by ending the recordset (rec) right after the data import and then start a new one only for the altering of the data, but I keep on getting the same error.

I also tested the INSERT INTO version you mentioned earlier, but despite having the memo field status preserved, it does not replace the * symbols with the linebreak and so on. Rather strange if you ask me since the same code used to work flawlessly before...

bah... still thanks for all the help. maybe someone has a clue whats wrong with this piece of code.
thanks,
A2k

 

[jal]

You should not be getting that error. Did you have the table open in another window when testing?

 

[jal]

Why are you opening a recordset? I don't even see where you make use of it. Don't open a recordset until you actually need one.

 

[Armitage2k]

Ladies and Gentlemen, I am stupid.

Instead of letting access look for ' * ' I maybe should adjust it to '*'.... but I give myself credits for figuring this out

THANKS A LOT for all the help. without you folks I would have never come this far!
Cheerts,
A2k