Link to home
Create AccountLog in
Avatar of Keking
KekingFlag for United States of America

asked on

import data to ms access from Excel

I am trying to write some VBA to import data from an excel file. I will not the starting row and column which is row 2 column 1 but I do not know the ending row.  I need it to stop once a null value has occured. This is what i have so far. It has been a long time since I have tried to do this. ugh.

Public Function InsertExcelData(filePath As String)
  On Error Resume Next

  Dim xl As Excel.Application
  Dim xlBook As Excel.Workbook
  Dim xlSheet As Excel.worksheet
  Dim strSQL As String
  Dim Row As Integer
  Dim Column1 As String
  Dim Column2 As String
  Dim Column3 As String
  Dim Column4 As String
  Dim Column As Integer
 
  Set xl = CreateObject("Excel.Application")
  Set xlBook = xl.Workbooks.Open(filePath)
  Row = 2
  Column = 1
  MsgBox (xlSheet.Cells(Row, Column))
  strSQL = "INSERT INTO dbo_pror_tbl_CBH_Upload (Member, Patient, Doc_Number,Insured_SS_Number,Amount)" & _
           "VALUES (" & xlSheet.Cells(Row, Column) & "," & xlSheet.Cells(Row, (Column + 1)) & "," & xlSheet.Cells(Row, (Column + 2)) & "," & xlSheet.Cells(Row, (Column + 3)) & "," & xlSheet.Cells(Row, (Column + 3)) & ")"
  DoCmd.RunSQL (strSQL)
  Set xl = Nothing
  Set xlBook = Nothing
  Set xlSheet = Nothing
End Function
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

You might find it easier to just import the entire Excel spreadsheet into Access (using TransferSpreadsheet) and then work within Access to get the data you need.
Avatar of Keking

ASKER

I thought of that but there are many files (50 a month or more) that need to append in and there are many rows that I do not need, like the addresses and business names.... the sums at the bottom..ect.
<and there are many rows that I do not need, like the addresses and business names.... the sums at the bottom..ect. >

in your original post you said

< I need it to stop once a null value has occured. >


you need to post sample excel file for us to figure out which rows you need to import.
change sensitive info with dummy data.
Avatar of Keking

ASKER

actually I can do most of it.. I am mostly just having trouble getting the connetion to the workbook opened and to be able to get a look at the sheet and cells. If you could help me with the code to open the workbook and look at say Cell row 3 column 1 I should be able to work out the rest. . I am at home and do not have access to the files right now.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Keking

ASKER

thank you!