Excel 2000 and vb - text & NumberFormat method 2

[vza]

I am currently working on a VB application which transfers data from an Excel spreadsheet to an Access DB table. I am able to transfer most of the data except for a few cells in one of the spreadsheet columns. The problem is that most of the values within the column have number representations while some exist which are a combination of both numbers and letters. These combination cells do not copy over to the DB. The field Data type within the DB table is set to Text...
I tried to set all values within the excel spreadsheet column to "Text" using the following lines of code:

      Columns("C:C").Select
         With Selection
            .NumberFormat = "@"
         End With

when I transfer the data the same problem still arises....

Any advice?? Any reponses would be greatly appreciated.

Thanks
-vza

 

[SkipVought]

Hi,

Need to insert a TIC '

For Each s In Selection
  With s
    If IsNumeric(.Value) Then .Value = "'" & .Value
  End With
Next

Skip,

http://www.TheOfficeExperts.com

 

[vza]

Thanks for the quick reply!

the s stands for a cell I presume??

Thanks
-vza

 

[SkipVought]

it is a range object that represents one of the cell in the Selection

Skip,

http://www.TheOfficeExperts.com

 

[vza]

Do I declare s as a range like so?

Dim s as range

I tried this method and my VB app freezes.

Also one other question...If i use the following formula to locate the last USED cell within a column:

Range("A635536&quot.End(xlup).Select
how could I move to the cell below utilizing code?
(i do not know where this cell will be during runtime)

I appreciate all your assistance with this issue...
As I am sure you can tell I am not to familiar with Excel VBA...

Thanks
-vza

 

[xlbo]

you may need to use
dim s as excel.range
...??? not sure as I don't really do VB (VBA only)

to go to the cell below the last cell, use
Range("A635536&quot.End(xlup).offset(1,0).Select

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[vza]

xlbo,
Thanks for your help
I tried this alteration:

Dim Rng as Excel.Range
Columns("C:C").Select
  For Each Rng In Selection
    With Rng
      If IsNumeric(.Value) Then .Value = "'" & .Value
    End With
  Next

My App still freezes...I have to goto Task Manager and end the EXCEL.EXE process to stop the App.

The Offset worked perfectly!
Thanks
-vza

 

[vza]

I realized why this is happening....
This loop adds a TIC to the whole C column
I think if i can set the boundaries correctly I should be all set....imma try and work with it....

Thanks
-vza

 

[xlbo]

for your boundaries, you can use:

for each s in range("C1:C" & range("C65536&quot.end(xlup).row)
With s
If IsNumeric(.Value) Then .Value = "'" & .Value
End With
Next

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[vza]

xlbo,
Thanks for all your help!
everything is working nicely...
Here is how it looks:

Range("C2:C" & Range("C65536").End(xlUp).Row).Select
    For Each Rng In Selection
        If Not IsNumeric(Rng.Value) Then 
             Rng.Value = "'" & Rng.Value
        End If
    Next Rng

much appreciation.
Enjoy the rest of your week.

Thanks
-vza

 

[vza]

The TIC was applied to the Excel spreadsheet column as SkipVought suggested...the cells were still not added in the DB transfer...this is frustrating....I still do appreciate all the effort though

Thanks
-vza

 

[SkipVought]

Make sure that you SAVE the workbook before importing.

Skip,

http://www.TheOfficeExperts.com

 

[vza]

I have to save the workbook (which requires saving due to changes) because if I do not an error which states that the workbook is still open appears during the import to the DB...maybe that is where my problem is....
I use RecordSets to do the physical transfering of data to the Access DB....could that be causing the problem?
Here is my import code:

Private Sub ImportButton_Click()
    ' Variables
        Dim i As Integer

    ' Connection to Excel Spreadsheet/Access Database
        ' Excel
            Dim CnXL As New ADODB.Connection
            CnXL.Provider = "Microsoft.Jet.OLEDB.4.0"
            CnXL.ConnectionString = "Data Source = '" & ConvertForm.ExcelText.Text & "'; Extended Properties = Excel 8.0;"
            'On Error GoTo Error
            CnXL.Open
        ' Access
            Dim CnAX As New ADODB.Connection
            CnAX.Provider = "Microsoft.Jet.OLEDB.4.0"
            CnAX.Open ConvertForm.AccessText.Text, LoginForm.UsernameText.Text, LoginForm.PasswordText.Text
    ' RecordSets
        ' Excel
            Dim rsXL As New ADODB.Recordset
        ' Access
            Dim rsAX As New ADODB.Recordset
    ' Open Recordsets
        rsXL.Open "Select * From [Sheet1$]", CnXL, adOpenStatic
        rsAX.Open "Select * From Conversions", CnAX, adOpenKeyset, adLockOptimistic
    ' Copy data from Excel Spreadsheet to Access Database
        Do While Not rsXL.EOF
            rsAX.AddNew
            For i = 0 To rsAX.Fields.Count - 1
                rsAX.Fields(i).Value = rsXL.Fields(i).Value
            Next i
            rsAX.Update
            rsXL.MoveNext
        Loop
    ' Close Recordsets/Connnections
        rsXL.Close
        rsAX.Close
        Set rsXL = Nothing
        Set rsAX = Nothing
    Exit Sub
' Error Condition
Error:
    MsgBox "If requested Excel Spreadsheet is still open," & vbCr & "save and close file. Then continue the Import process.", vbInformation, "Spreadsheet Import Error"
End Sub

 

[GeekGirlau]

Have you tried using the Access TransferSpreadsheet function? This allows you to import the entire spreadsheet using a predefined import specification, without having to import the data cell by cell.

 

[vza]

GeekGirlau,

I will look into that.....Thanks for the tip!

-vza

 

[vza]

GeekGirl,

That actually worked REALLY well!!

Thanks Lots!
Enjoy your weekend!!

-vza

 

[vza]

GeekGirl,

Could I dynamically specify a range within the transferspreadshet function call...I do not specifically know the range needed during runtime....and the spradsheet transfers numerous blank rows upon completion.

Thanks
-vza

 

[GeekGirlau]

vza, the TransferSpreadsheet command allows you to specify a range. An example is as follows:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MyTable", "MySpreadsheetFile", True, "MyRange"

You can make a decision here to either have your code dynamically set that range prior to importing the data, or to import all the data then run a query that deletes any blank records.

 

[vza]

GeekGirl,

That is my only problem...how can I dynamically code a range?? Does it need to be in a string format??

Thanks
-vza

 

[vza]

Using the .UsedRange property I was able to find the dynamic range of a particular spreadsheet during runtime. I stored this value in a string variable and used this same variable within the TransferSpreadsheet Range argument. The trick was to convert the .UsedRange string into the acceptable TransferSpreadSheet Range format:

"SheetX!A1:B1"

where X, A, B are arbitrary numbers and the 1's represent the range call numbers.

Thanks,
-vza