appending an excel worksheet to existing access database Results


Sponsored Links:



How can I with the code below when the database opens and table2 already exist delete it and make a new table2?

On Error GoTo errHandler
Dim mExcelFile As String
Dim mAccessFile As String
Dim mWorkSheet As String
Dim mTableName As String
Dim mDataBase As Database
mExcelFile = App.Path & "Book1.xls"
mAccessFile = App.Path & "Db2.mdb"

mWorkSheet = "Sheet1"
mTableName = "Table2"
' Below you may use "Excel 7.0" or 8.0 depending on your installable ISAM.
Set mDataBase = OpenDatabase(mExcelFile, True, False, "Excel 5.0")
mDataBase.Execute "Select * into [;database=" & mAccessFile & "]." & mTableName & _
" FROM [" & mWorkSheet & "$]"
MsgBox "Done. Use Access to view " & mTableName
Exit Sub
errHandler:
If Err.Number = 3010 Then
MsgBox mTableName & " already exist." & vbCrLf & _
"Delete " & mTableName & " first or use another table name."
Else
MsgBox Err.Number & " " & Err.Description
End If




Hi Everyone,

I have never posted before so I hope I do this correctly. I have searched for days and only found parts of what I need so I hope someone can help me because I know nothing about VBA.

I need to click a button on Excel to append an Excel named range to an existing Access table.

Access database: K:DatabaseWeekly Report.mdb
Access table: ExcelData
Excel worksheet: Report
Excel named range: Data

Thanks for your time.




Hi everyone,
I'm a newcomer to the forum and also (relatively) to these latest Office offerings. I apologise in advance if this is somethin covered by an existing thread. I spotted a few that are close but not the same.
I have an Excel worksheet (that I created from an old Access database because it'e asier, for me, to manipulate, parse data etc... in Excel.). In any event, I have this Excel table that, as far as I can see is clean and tidy. I tried to imprt/append it to an existing (although blank) Access table and failed with error messages, effectively saing that 0 records had been deleted and 0 appended. I have also imported same data to a new table and then tried an append query with similar results. There are no blank rows and no blank columns (although I cannot specify the range). As far as I can see there are no type mismatches - 80% of fields are text, three are numbers, one is memo and one hyperlink. The target database is a a slightly amended (fields added to Customer table) of the Sales Pipeline template from Microsoft.
I'd be very grateful if someone could help.
Regards,
denis




Hi All

I have searched the forums and can not find anything that suits my needs.

What these are as follows.
I need a command button on a form that will allow me to chose an .xls from a directory and add to the existing data in an access .mdb table to an access mdb file.

I am using access 2007 but db is saved in 2003 mdb format and using excel 2007 but import is from 2003 format.

Database name = rental.mdb
Database table name = bookings
There are approx 25 fields

The field names in the excel worksheet are the same as the field names in the mdb table although some fields will be blank.

I have created the form command button (cmdbutton1) but can't get any code to work.

Any help would be appreciated.

Evan




Hello, I am new to VBA and I'm trying to write a VBA Module in Access that runs a SQL query and takes that result and appends it into an existing Excel report. Thus far I've been able to get it paste the data but it puts it in the wrong spot, the correct columns but wrong rows on Excel sheet. I'm looking for the correct VBA code that will look for the 1st available blank cell and then paste the data there. As each month the data will move down one row until the year is over then it will revert to the top and start all over again. Below is my VBA code and I've attached a copy of the report where the data is supposed to paste, for your review. I would greatly appreciate any help you could offer. Thank you in advance for your assistance.

P.S. The data is suposed to pase to cells F12-I12


	Code:
	Sub GetAccessData_With_SQL_GetObject_With_Excel()
'Step 1: Declare your Variables
    Dim MyConnect As String
    Dim MyRecordset As ADODB.Recordset
    Dim MyQueryDef As DAO.QueryDef
    Dim MyDatabase As DAO.Database
    Dim MySQL As String
    Dim MyRange As String
 
    Dim Db As Database
    Dim xl As Excel.Application
    'Set xl = New Excel.Application
    Set xl = CreateObject("Excel.Application")
    Dim xlwkbk As Excel.Workbook
    Dim xlsheet As Excel.Worksheet
    Dim strInput As String
    Dim strMsg As String
    'Sep 2: Declare your connection string
    MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info = False;Data Source= Q:WWC CommonTAMPA FilesLSG
Month-End FG Inventory Excess-Reserve Analysis.accdb; User ID = Admin;"
    'Step 3: Build Input Box
    strMsg = "What Fiscal Month?"
    strInput = InputBox(Prompt:=strMsg, Title:="Period")
    strInput = Chr(34) & strInput & Chr(34)
    MsgBox (strInput)
    'Step 4: Build Your SQL Statement
    MySQL = "Select [slq-Item count].[Fiscal Year],[slq-Item count].[Fiscal Month], " & _
            "Sum([slq-Item count].CountOfitem) As ItemCount,Sum([slq-Item count].[SumOfGross Units]) As Units " & _
            "From [slq-Item count] " & _
            "Group By [slq-Item count].[Fiscal Year],[slq-Item count].[Fiscal Month] " & _
            "Having([slq-Item count].[Fiscal Year]=2012 And [slq-Item count].[Fiscal Month]= " & strInput & ")"
    'Step 5: Instantiate and specify your recordset
    Set MyRecordset = New ADODB.Recordset
    MyRecordset.Open MySQL, CurrentProject.Connection    'adOpenStatic, adLockOptimistic)
    'Step 6: Instantiate Excel. If Excel isn't Loaded, Error # 429 occurs.
    Set xl = GetObject(, "Excel.Application")
    'Step 7: Open ItemIdCount.xlsx
    'Set xlwkbk = xl.Workbooks.Open("Q:WWC CommonTAMPA FilesItemIDCount.xlsx", , True)
    Set xlwkbk = xl.Workbooks.Open("Q:WWC CommonTAMPA FilesItemIDCount.xlsx")
    Set xlsheet = xlwkbk.Worksheets("ItemIdCnt")
    xl.Visible = True
    xlwkbk.Windows(1).Visible = True
    'Step 8: Find First empty Row and use that to build a dynamic range
    xlsheet.Select
    MyRange = "F" & _
   ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row 1 (This is where I get the error message)
    'Step 9: Copy the recordset to First Empty Row
    ActiveSheet.Range(MyRange).CopyFromRecordset MyRecordset
    'ActiveWorkbook.Save
    'ActiveWorkbook.Close
    'Step 10: e variables
    Set xlsheet = Nothing
    Set xlwkbk = Nothing
    Set xlApp = Nothing
    Set Db = Nothing
    MyRecordset.Close
 
 
 
 
End Sub
Sub ItemCount()
End Sub





*EDIT: see 3rd post for solution/solvation

Hi,

Right now I am building a database with items, coming from local dealers.
We send them a locked excel sheet, which they feed with dealer information and item information. In the excel sheet, this is being separated (in the background) in 2 different sheets. I have my master sheet, which is being maintained by the dealer, and I have 2 slave sheets, which I import into Access.

In my first steps into VBA I learned to copy/paste code to import those files into 1 table. (See code below).
I am a virtual VBA n00b, so know nothing about it, yet.
I will have to, though, since I plan to set up this database in VBA.


	Code:
	Sub Link_To_Excel()
'Macro Loops through the specified directory (strPath)
'and links ALL Excel files as linked tables in the Access Database.
 
Const strPath As String = "serverpathNew Files for upload" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
 'Loop through the folder & build file list
strFile = Dir(strPath & "*.xlsx")
While strFile  ""
 
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
 
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'appending to tables called DealerLists and DealerContacts
For intFile = 1 To UBound(strFileList)
DoCmd.TransferSpreadsheet acImport, , _
"DealerLists", strPath & strFileList(intFile), True, "parts!A1:E229"
DoCmd.TransferSpreadsheet acImport, , _
"DealerContacts", strPath & strFileList(intFile), True, "contact!A1:F2"
Next
MsgBox UBound(strFileList) & " Files were Imported"
 
End Sub




Now I have 3 challenges:One is, to know up front which dealer (information) is being imported. In the DealerContacts table, I have a lot of dealers. And if I get a new sheet of an existing dealer, I want to know if it already exists in the list or not. I want to be able to refresh the information, if the dealer already exists.How do I read out a specific cell, to be able to decide whether I should update or append the information? And how do I update the record, if it already exists? The second is, to remove old information in the item table and append new information.Once I have the dealer number, recorded in the previous session, how can I use this to delete all related records in an item list? Kind of: build a query, related to this dealer number, and delete all records with this specific number? Last but not least: to make sure I do not import empty cells (dealers have 228 records to their availability, but will seldom use that), I have a counter in sheet 4. This sheet contains only title and a number; number of lines filled with data.Essentially this is the same problem as 1., because I need to feed the information of this specific cell to a variable, which I then use to set the TransferSpreadsheet Command with the right amount of lines: import "parts!A1:E" & NumberOfLines This is the code I was trying, but it doesn't seem to work (fails at first line):

	Code:
	 
'Read the number of lines in the file 
Dim xl As Excel.Application
 
Dim xlsht As Excel.worksheet
Dim xlWrkBk As Excel.Workbook
Dim xlCell As Double
 
Set xl = CreateObject("Excel.Application")
Set xlWrkBk = GetObject(strPath & strFileList(intFile))
Set xlsht = xlWrkBk.Worksheets("Counters")
Set xlCell = xlsht.cells(2, "A")
 
DoCmd.TransferSpreadsheet acImport, , _
"DealerLists", strPath & strFileList(intFile), True, "parts!A1:E" & NumberOfLines
 
Set xl = Nothing
Set xlWrkBk = Nothing
Set xlsht = Nothing
Set xlCell = Nothing 

Who can help me out in this?

Thanks in advance,
Timoo