VBA code not importing excel data as intended to

[CoolFactor]

The goal is to import excel data from the various columns in the worksheet knowingly that the first row is generally the column names in the worksheet. I created a table in Access with the columns that should match up with excel column names.

This is the part of the code that I'm having issues with and after this is the whole code:

rst("PEODirectorate") = ExcApp.Worksheets("Sheet1").Range("A2").Value
rst("Profile") = ExcApp.Worksheets("Sheet1").Range("B2").Value
rst("System") = ExcApp.Worksheets("Sheet1").Range("C2").Value
rst("Hull") = ExcApp.Worksheets("Sheet1").Range("D2").Value
rst("HullNumber") = ExcApp.Worksheets("Sheet1").Range("E2").Value


The Whole Code:

Option Compare Database

Private Sub cmdStar_Click()
On Error GoTo Err_cmdStar_Click
Dim NewRow As Integer
Dim R() As String
Dim InFile As String
Dim fileToOpen As String
Dim db As Database
Dim rst As Recordset
Dim SeqNum As Integer
Dim Continue As Boolean
Dim RowNum As Integer
Dim CellToRead As String
Dim Pos, LastPos, FileLen As Integer

Dim ExcApp As New Excel.Application
Dim newWBooks As Excel.Workbooks

Set newWBooks = ExcApp.Workbooks

'Dim myAcces As New Access.Application
Dim myFD As FileDialog
'Dim myFname As String
'Dim myFPath As String

Set myFD = FileDialog(msoFileDialogOpen)

myFD.Filters.Add "Excel Files", "*.xls", 1
myFD.AllowMultiSelect = False

With myFD
If .Show = -1 Then
fileToOpen = .SelectedItems.Item(1)

newWBooks.Open fileToOpen

'-1 = user selected open
' do what you will with newWB
Else
'! -1 = User canxed
MsgBox ("You must select an Excel Workbook to continue.")
GoTo NoSelection
End If

End With

StrLen = Len(fileToOpen)
Pos = InStr(1, fileToOpen, "\")
LastPos = Pos
If Pos > 0 Then
Do
Pos = Pos + 1
LastPos = InStr(Pos, fileToOpen, "\")
If LastPos <> 0 Then
Pos = LastPos
End If
Loop Until LastPos = 0
End If
InFile = Mid(fileToOpen, Pos)

Set db = CurrentDb
' first populate the primary table - holds the most-frequently used data
Set rst = db.OpenRecordset("tblcold")
rst.AddNew
' starts with the data in the sheet1 sheet

ExcApp.Worksheets("Sheet1").Activate

'Worksheets("Sheet1").Activate

rst("PEODirectorate") = ExcApp.Worksheets("Sheet1").Range("A2").Value
rst("Profile") = ExcApp.Worksheets("Sheet1").Range("B2").Value
rst("System") = ExcApp.Worksheets("Sheet1").Range("C2").Value
rst("Hull") = ExcApp.Worksheets("Sheet1").Range("D2").Value
rst("HullNumber") = ExcApp.Worksheets("Sheet1").Range("E2").Value

rst.Update
rst.Close

' and that's everything - we can close the database and the workbook!

'Set newWSs = Nothing
newWBooks.Close
Set newWBooks = Nothing
ExcApp.Quit
Set ExcApp = Nothing
db.Close

MsgBox "Excel Workbook Import Successfully Completed"
'
NoSelection:
Exit_cmdStar_Click:
Exit Sub

Err_cmdStar_Click:
MsgBox Err.Description

Set newWSs = Nothing
newWBooks.Close
Set newWBooks = Nothing
ExcApp.Quit
Set ExcApp = Nothing
db.Close

Resume Exit_cmdStar_Click

End Sub

 

[Golom]

OK. So what does "having issues with" mean?

Is it throwing an error? Getting no values? Getting the wrong values?

 

[CoolFactor]

I'm getting data conversion Error:

I also changed a part of the code presented above:


With ExcApp.ActiveSheet

'ActiveSheet'

rst("PEODirectorate") = ExcApp.ActiveSheet.Range("A2:A45")
rst("Profile") = ExcApp.ActiveSheet.Range("B2:B45")
rst("System") = ExcApp.ActiveSheet.Range("C2:C45")
rst("Hull") = ExcApp.ActiveSheet.Range("D245")
rst("HullNumber") = ExcApp.ActiveSheet.Range("E2:E45")

rst.Update
rst.Close

End With

 

[bubba100]

This has been posted twice thread702-1477832

 

[Golom]

You are attempting to assign 44 cells in Excel to a single field in the database.

What are you expecting to happen with that?

 

[CoolFactor]

Golom

Actually, I'm trying to figure out how to capture everything in
Column A, Column B, Column C etc... to a temp table in Access in their respective fields. I appreciate your input, I wasn't sure if what I was doing was right or not.

I like the first part of my code because I can open an arbitrary workbook but I'm having trouble putting together how I can capture the data in excel and drop into an access table.

Ideally I would like the column names in whatever excel spreadsheet to become my field names in my access table as well capture the data in their respective columns from the excel worksheet into the respective fields in the temporary access table. It's kind of like copy and paste except for that the first columns in the spreadsheet would become the fields names in my table. The table would also require a primary key that is just an autonumber.


Once again I really like the first part of the code because it allows me to open any workbook I want.

I thank you for your help Golom, I'm just not an experience programmer like yourself.

 

[Golom]

Although it is possible to modify your code to do that, there is a TransferSpreadSheet Function that is designed to do that with one command.

See if that will suit your needs.

 

[CoolFactor]

how would you put it together as an example? I apologize if this is to much to ask.

 

[Golom]

If you click on the link in my post, Microsoft has provided an example.

 

[CoolFactor]

I did follow your link and this is what I have so far:

I do want this to be a specific workbook at all and I want it to transfer to a temp table that access creates on it's own

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
DoCmd.TransferSpreadsheet acImport(acSpreadsheetTypeExcel8, TableName, Filename, True)
End Sub

 

[PHV]

Put the cursor inside the TransferSpreadsheet word in your code and, please, press the F1 key.

 

[CoolFactor]

I understand that portion and I thank you for your time but at the moment this is what I have:

Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, TableName, Filename, True
End Sub

I want it to transfer the data into a new table in Access with an autonumber primary key and when it's all done close the workbook that I'm transfering the data from. I want it to be any excel file of choice though.

 

[bubba100]

DoCmd.TransferSpreadsheet acImport, 8, "Table1", sFileName, True, "UpLoad!A3:M127"
Table1 is my temp table.
sFileName is from the commondialog box used to select the Excel file.
"Upload" is the sheet name.
A3:M127 is the range on the "Upload" sheet I want copied from my Excel file to my table called Table1.

 

[CoolFactor]

When I try to execute this it kicks up the error with
the sFileName that it requires a file argrument

Also I want it to create new table in access, I really don't want to define the table

Thank you

 

[bubba100]

You won't need to open the Excel file. From your code above replace my sFileName with your fileToOpen. You will need to create the table first. The example I provided is one that I use and was provided to be an example.
Good luck.