Access vba code to Export tbl data to Excel spreadsheet?

I am at my ropes end with this... I am very new to vba.

I have a db with a tbl and a form.
I have an Excel Workbook with multiple spreadsheets.
In a spicific spreadsheet I add data to the bottom of a long list of data.
That data starts in Column A and goes through Column T.
Every Row is a Single record(Company name, title, phone number, etc.)

spreadsheet format: Code: record ID first name last name title company address and so on 84395 John Smith President Random Inc. 555 Main St yes NOTE: the record ID's will already exist before the data gets to me, and will need to stay the same.

The db that I am using: "my_db"
The tbl in "my_db" is: "my_tbl"
The already existing Excel Workbook: "my_excel"
The spreadsheet to be used in "my_excel" is: "my_spreadsheet"


I want to:
push a button on my form and the following happens...
- all the data in "my_tbl" is selected and copied(but NOT the headers)

- paste copied data(something comparable to paste special/CSV) into the first empty cell of Column A in "my_spreadsheet". [The Columns in "my_spreadsheet" have formulas and formatting that should not be altered]

- I do not want "my_excel" to be closed after this operation.

NOTE: "my_excel" will already be open, but minimized.


I have tried and tried... now I just want to put in the code so that I can shave minutes off of this task, which I do all day.

Please help.


Post your answer or comment

comments powered by Disqus
Hello Everyone,

I know it can be done and I have seen some code snippets before, but how can I use Access vba code to count all forms, tables, queries, reports, vba modules, objects to get a total count of each for an Access database?

Any help is greatly appreciated.

Thanks,

Kurt


Hi folks,

I'm in need of some VBA code to do the following from a Microsoft Access Table (version 2007).
The idea is to attach the code to a macro in Access and attach to a button on a Access form.
I need to create/export two Excel spreadsheets from the table named "Main" and place them to a specified
location (For Example: C:my exports). I've done transfer to spreadsheet in the past but in this case I need to do some specific checks and manipulation so help is greatly appreciated!

Table 'Main' has 6 columns (No Primary Keys):
Section: Text
Page #: Text
Item: Text: Text
New_Page #: Text
Page_Sort: Number
Delete: (Check Box)

Spreadsheet #1:
Needs to look like this:
Item (Column A) Pages (Column B)
Give Kids a Smile 9
Concentrix Handpieces New! 468
430-Series Handpieces 468
BURS Specialty 52, 54_55, 70_71
BURS 49_127
BURS Carbide 49_79

It needs to first evaluate the 'Delete' field (Check Box) and ignore any records with a check.
It then needs to evaluate the 'New_Page #' field and ignore any blank records.
It then needs to evaluate where the 'New_Page #" field is blank (or null) and the 'Delete' field is ALSO blank and
if that scenario is true, then a 'Msg Box' should appear warning the user "Files Not Complete, Do you want to proceed?"
(FYI - I will have a report for them to view those instances outside of this process)
If they choose to proceed then:

Basically it needs to take all records for given 'Item' and truncate the data in the 'New_Page #' field and
comma seperate them.

Spreadsheet #2:
Needs to look like this:
Section (Column A) Page Numbers (Column B)
COVER, STORY, & INTRO 1-17
Total Health 18-24
Acrylics 25-30
Alloys 31-35
Anesthetics 36-44
Articulating 45-48
Burs 49-127
CAD/CAM 128-138
Cements & Liners 139-159

The evaluation on this one is a little tricker as the related fields are text as opposed to a number.
It basically needs to search the 'New_Page #" field for the smallest number (thought text...as some records could have underscores)
and search for the largest number and add the "-" (Hyphen) between them.

This process also needs to ignore any records with a check in the 'Delete' column.
This process should also ignore any records with a blank (or null) in the 'New_Page #' field.

HELP!
I hope I gave enough info but if I missed anything please ask and I will gladly answer and I appreciate all the help!
Sample of what table "Main" looks like:

Section Page # Item New_Page # Page_Sort Delete COVER, STORY, & INTRO 9 Give Kids a Smile 10 9 0 COVER, STORY, & INTRO 12_15 What’s NEW!
12 -1 Total Health 18_24 TOTAL HEALTH New!
18 0 Total Health 19 VELscope Vx
19 0 Total Health 20_22 Sleep Complete New!
20 0 Total Health 23 Microlux DL
23 0 Total Health 23 DNA Testing
23 0 Total Health 23 Salivary DNA Tests
23 0 Total Health 24 OralDNA
24 0 Total Health 24 OraRisk HPV Salivary DNA Test
24 0 Total Health 24 MyPerioPath Salivary DNA Test
24 0 Acrylics 25 Coe Tray Plastic
25 0 Acrylics 25 Fastray
25 0 Acrylics 25 Sapphire Impression Material
25 0 Acrylics 25 Easy Tray
25 0 Acrylics 25_30 ACRYLICS & RELINE MATERIALS
25 0 Acrylics 25 Rimseal
25 0 Acrylics 25 Hydroplastic
25 0 Acrylics 25 Jet_Tray
25 0 Acrylics 26 Hydro_Cast
26 0 Acrylics 26 Paladon Ultra New!
26 0 Acrylics 26 Tissue Conditioner
26 0 Acrylics 27_28 Hard Reline Materials
27 0 Acrylics 27 Hygenic Perm
27 0 Acrylics 27 Chairside Reline Material
27 0 Acrylics 27 Ufi Gel Hard C
27 0 Acrylics 27 Coe Rect
27 0 Acrylics 27_29 Reline Materials
27 0 Acrylics 28 Dentusil Denture Reline
28 0 Acrylics 28 Silk Line
28 0 Acrylics 28_29 Soft Reline Materials
28 0 Acrylics 28 Truliner
28 0 Acrylics 29 Sofreliner
29 0 Acrylics 29 Acraweld Repair Material
29 0 Acrylics 29 Z_Bur
29 0 Acrylics 29 Versa_Soft
29 0 Acrylics 29_30 Repair Materials
29 0 Acrylics 29 Trusoft
29 0 Acrylics 29_30 Denture Repair Materials
29 0 Acrylics 30 DuraLay
30 0 Acrylics 30 Dura Seal
30 0 Burs 49_127 BURS 53_128 49 0 Burs 52 BURS Specialty 50 52 0 Burs 54_55 BURS Specialty 68_78 54 0 Burs 70_71 BURS Specialty 88_98 70 0 Equipment- Small 368 Microetcher
368 -1

Trying to get vba code to enter in repetive data.

Basically there is two tables that this deals with, Chemical and Chemical Details Table.

Chemical has this field called Method, which determines what chemicals are tested, hence the parameters field in chemical details, which has a record for each chemical.

Since the methods always use the same chemicals, i want the vba code to generate the records with the paramaters automatically depending on which method is chosen.

The problem is getting the data entered in the ChemDetails tables so it corresponds with Chemical Table, meaning the primary key doesn't match up, Chemical_DataID, which is a autonumber.

I tried setting the Chemical_DataID in the details table to what the Chemical table would have, but that doesn't work, it doesn't seem to let u change the number.

Any Ideas?
Thanks Greg

Private Sub Method_AfterUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim tempID As Long
Dim rstDet As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Chemical", dbOpenDynaset)
Set rstD = dbs.OpenRecordset("Chemical_Details", dbOpenDynaset)

rst.MoveLast
rst.AddNew
tempID = rst![Chemical_DataID] - 1
If rst![Method] = "EPA 602" Then
rst.MoveLast
rstD.AddNew
rstD![Chemical_DataID] = tempID
rstD!Parameter = "t butylmethylether"
rstD.Update
rstD.AddNew
rstD!Parameter = "Benzene"
rstD![Chemical_DataID] = tempID
rstD.Update
rstD.AddNew
rstD!Parameter = "Toluene"
rstD![Chemical_DataID] = tempID
rstD.Update
rstD.AddNew
rstD!Parameter = "Ethylbenzene"
rstD![Chemical_DataID] = tempID
rstD.Update
rstD.AddNew
rstD!Parameter = "m + p Xylene"
rstD![Chemical_DataID] = tempID
rstD.Update
rstD.AddNew
rstD!Parameter = "o-Xylene"
rstD![Chemical_DataID] = tempID
rstD.Update
End If
End Sub

Hi All,

I need to export a set of 15 queries into excel for the specified range created in excel file. All these queries belongs to different criteria's and need to go separtely to their specified range. Is there is any way to export into excel using VBA codes in access.
I have some knowledge about docmd transferspreadsheet but its for only one query and its taking more time to export also.

Hi guys,
Earlier I had a excel file with many columns.
I wrote a code in Excel VBA to fill up the last two columns according to the data in the other columns of the row.
It worked fine.
Now, the whole thing is transferred to an Access table.

Pls help me convert this Excel VBA code to Access VBA.

Code: Sub GeneratePorEngID() Range("S2:T5000").Clear Dim PrevTaskID As Integer Dim intNO As Long Dim intCount As Integer intNO = 1 PrevTaskID = 1 For i = 2 To 5000 'If PrevTaskID = 1 Then intNO = 1 If Cells(i, "J") "" Then If PrevTaskID = Cells(i, "J") Then Cells(i, "S") = intNO intNO = intNO + 1 intCount = intCount + 1 Else For j = i - intCount - 1 To i - 1 Cells(j, "T") = intNO intNO = intNO + 1 Next Cells(i, "S") = intNO intNO = intNO + 1 intCount = 0 PrevTaskID = Cells(i, "J") End If End If Next End Sub Thanks a ton in advance

I would like to export the bounded data in a subform ( this subform is only bounded to a particular set of fields in the table) to a text file on the click of a button . Could someone help me with a suitable VBA code pls ?

Good day Folks,

I have some code that exports data to an Excel spreadsheet, which I got working fine, I needed to create the same process for some other data so I created a similar process for that data. The problem I am experiencing is that whenever I run the code to export both sets of data to Excel the second created process always errors out with the error 91, having read what access pops up in the help menu, it states the following:

Object variable or With block variable not set (Error 91)



There are two steps to creating an object variable. First you must declare the object variable. Then you must assign a valid reference to the object variable using the Set statement. Similarly, a With...End With block must be initialized by executing the With statement entry point. This error has the following causes and solutions: You attempted to use an object variable that isn't yet referencing a valid object.
Specify or respecify a reference for the object variable. For example, if the Set statement is omitted in the following code, an error would be generated on the reference to MyObject: Dim MyObject As Object ' Create object variable.Set MyObject = Sheets(1) ' Create valid object reference.MyCount = MyObject.Count ' Assign Count value to MyCount.You attempted to use an object variable that has been set to Nothing. Set MyObject = Nothing ' Release the object.MyCount = MyObject.Count ' Make a reference to a released object.Respecify a reference for the object variable. For example, use a new Set statement to set a new reference to the object.
The object is a valid object, but it wasn't set because the object library in which it is described hasn't been selected in the References dialog box. Select the object library in the Add References dialog box.
The target of a GoTo statement is inside a With block. Don't jump into a With block. Make sure the block is initialized by executing the With statement entry point. You specified a line inside a With block when you chose the Set Next Statement command. The With block must be initialized by executing the With statement. For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).


however I do have the correct reference to Excel in my module which is "Microsoft Excel 11.0 Object Library, otherwise the first created code would not work. it always happens on the second created code, which has the exact same references. Here's the code and the one that is always highlighted is in red


	Code:
	 
With Objsheet 
        .Rows("1:1").Font.Bold = True
        .Rows("1:1").Font.Underline = xlUnderlineStyleSingle 
        .Rows("1:1").Select 
        Selection.Insert Shift:=xlDown
        Range("A1").Select
        ActiveCell.FormulaR1C1 = "Camera Form Code 03_Q"
        Range("B1").Select 
        ActiveCell.FormulaR1C1 = "As At Date :"
        Range("D1").Select 
        Selection.NumberFormat = "@"
        With Selection
            .HorizontalAlignment = xlLeft 
            .VerticalAlignment = xlBottom 
            .ReadingOrder = xlContext
        End With
End With

Any suggestions as to why this keeps happening, because I can't been able to work out from the help information what the problem is.

Thanks in advance

John

I have a VBA code written to call an Excel macro. The code runs, and it runs the Excel macro just fine, but for some reason in Access the code is erroring on the Excel macro even though it ran. Not sure what is happening. It is erroring on the red line of code. I did not write the Excel code, but it doesn't seem to be the issue. Any ideas what would make this line error even though the Excel macro runs fine?

Access Code:

	Code:
	Function QMFQueries()
Dim xl As Object
'Step 1:  Start Excel, then open the target workbook.
    Set xl = CreateObject("Excel.Application")
    xl.Workbooks.Open ("C:REPORT_DATAChronicUnitsBuildSheetsGet QMF Data.xlsb")
'Step 2:  Make Excel visible
    xl.Visible = True
'Step 3:  Run the target macro
   xl.Run "Get_QMF_Data_Nationals"
'Step 4:  Close and save the workbook, then close Excel
    xl.ActiveWorkbook.Close (True)
    xl.Quit
'Step 5:  Memory Clean up.
    Set xl = Nothing
End Function

Excel Code:

	Code:
	Sub Get_QMF_Data_Nationals()
   Application.EnableEvents = False
   Application.DisplayAlerts = False
   Dim BeginTime As Double
   BeginTime = Timer
   Calculate
   Sheets("NATIONALS").Shapes("Autoshape 101").Visible = False
   Sheets("NATIONALS").Shapes("Autoshape 102").Visible = True
   Application.ScreenUpdating = True
   Set QMFWin = CreateObject("QMFWin.Interface")
   Stat = QMFWin.InitializeServer("eProd", "", "", False)
   If Stat  0 Then
   Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True
   Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False
   [HOME1].Activate
       MsgBox ("Unable to Initialize QMF Server.  " + QMFWin.GetLastErrorstring() + "     ")
       Set QMFWin = Nothing
       Application.EnableEvents = True
       End
   End If
   cnt1 = 0
   rwcnt1 = 0
   Do
   starttime = Timer
   procname1 = Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column)
   runproc1 = UCase(Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column - 1))
   If runproc1  "YES" And runproc1  "Y" Or procname1 = "" Then GoTo 1
   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column).Interior.Color = 65535
   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column + 1).Interior.Color = 65535
 
   ProcID = QMFWin.InitializeProc(2, procname1)
   If ProcID < 0 Then
   Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True
   Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False
       MsgBox ("Unable to Initalize Proc.  " + QMFWin.GetLastErrorstring() + Chr(10) _
       + "Check the file name and make sure that it is in the path specified.")
       Set QMFWin = Nothing
       Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True
       Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False
       [HOME1].Activate
       ActiveWorkbook.Save
       Application.EnableEvents = True
       End
   End If
 
   Stat = QMFWin.RunProc(ProcID)
   If Stat  0 Then
       Application.WindowState = xlMaximized
       MsgBox (QMFWin.GetLastErrorstring() + Chr(10) + "A window will appear showing the procedure called and the date range
global variables used." + Chr(10) _
       + "Please check the date range parameters and if correct, debug the proc and/or any queries using QMF for windows then
try again." + Chr(10) _
       + "This error may also have been caused by an application disconnect with the server.")
       QMFProcInfo.Label1.Caption = "GLOBAL DATE RANGE VARIABLES"
       QMFProcInfo.Label2.Caption = procname1
       QMFProcInfo.TextBox1.Text = ("&FROM = " + QMFWin.Getglobalvariable("FROM") + Chr(10) + "&TO  = " +
QMFWin.Getglobalvariable("TO"))
       QMFProcInfo.TextBox2.Text = (QMFWin.GetProcText(ProcID))
       QMFProcInfo.Show
       Application.WindowState = xlMaximized
       Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True
       Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False
       [HOME1].Activate
       ActiveWorkbook.Save
       Application.EnableEvents = True
       End
   End If
 
   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column).Interior.Color = 5296274
   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column + 1).Interior.Color = 5296274
   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column + 1) = Date & " " & Time
   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column + 1).HorizontalAlignment = xlCenter
   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column + 2) = Format(DateAdd("s", (Timer - starttime), "00:00:00"),
"h:mm:ss")
   cnt1 = cnt1 + 1
1:  rwcnt1 = rwcnt1 + 1
   Loop Until rwcnt1 > 30
   Set QMFWin = Nothing
Dim hours1 As String, minutes1 As String, seconds1 As String
   hours1 = Format(DateAdd("s", (Timer - BeginTime), "00:00:00"), "H")
   If hours1 = 0 Then hours1 = "" Else If hours1 = 1 Then hours1 = hours1 & " HOUR, " Else hours1 = hours1 & " HOURS, "
   minutes1 = Format(DateAdd("s", (Timer - BeginTime), "00:00:00"), "N")
   If minutes1 = 0 Then minutes1 = "" Else If minutes1 = 1 Then minutes1 = minutes1 & " MINUTE, " Else minutes1 = minutes1 &
" MINUTES, "
   seconds1 = Format(DateAdd("s", (Timer - BeginTime), "00:00:00"), "S")
   If seconds1 = 1 Then seconds1 = seconds1 & " SECOND" Else seconds1 = seconds1 & " SECONDS"
   ActiveWorkbook.Save
   Application.WindowState = xlMaximized
   Application.EnableEvents = True
   Application.DisplayAlerts = True
   Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True
   Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False
   [HOME1].Activate
   Call ActivateAccess
   End
 
2:  MsgBox ("Can't continue.  Unable to locate the required files to update this report.")
3:  Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True
   Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False
   Application.WindowState = xlMaximized
   Application.EnableEvents = True
   Application.DisplayAlerts = True
   [HOME1].Activate
   ActiveWorkbook.Close savechanges:=True
End Sub



Hello,

I'm working on an access database (Access 2003) and exporting queries to Excel (Excel 2002). The intention is to get the Access VBA code to format the excel file for me so that all the exports will all look the same, and allow for easy re-importing after other parties (users) have added data to the excel file.

The exporting is working OK, some formatting is working OK as well, however I have 2 major issues that are not working for me, however someone is bound to have come across this before:
How to hide collumns in Excel (from Access VBA) specifying the collumn number (not the letter(s)). How to specify ranges in an excel worksheet (for formatting) using access VBA using the cell numbers (i.e. cell(1,2) , cell (1,4) which would be equivalent to B11). The idea of this function is to export a recordset (which can vary in size depending on the query run) and that it only formats the area in which the results are displayed.

As the recordset changes in size each time, I want to be able to automatically change the area(range) that is formatted. Retrieving the size of the recordset is not a problem, but using this result to change the range of cells that need reformatting is.

In addition there are some collumns that I want to hide to enable easier re-importing of the data later. (want to hide the data primary key)

The code that I am using is listed below. I have tried some things (as you can see from the code listed) and I've listed which don't work.

Looking forward to someone's bright idea on this one!

Thanks,

Arjanvb

'-------------------------------------------------------------------------------------------------------------------------

Public Function ExportXLS() As String

' Base methodology copied from: http://july-code.blogspot.com/2007/1...-to-excel.html

Dim oApp As Excel.Application 'in VBA : Tools : References " MS Excel reference library is required
Dim oWB As Excel.Workbook
Dim i As Integer
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim SQLstring As String
Dim CHANIDnum As Integer
Dim TempCounter As Integer
Dim RowStart As Integer 'to indicate which row Excel starts with putting data in
Dim CollumnStart As Integer 'to indicate which collumn Excel starts with putting data in
Dim NumRows As Integer
Dim TempString As String
Dim NumCollumns As Integer
Dim oSheet As Excel.Worksheet 'from excel example
Dim oRange As Excel.Range 'from excel example

'input channel ID number & other items for conversion to function later
CHANIDnum = 60

SQLstring = "SELECT tbl_QUES.CHANQNUM, tbl_QUES.QUESTEXT, tbl_QUES.ANSWTEXT, tbl_QUES.DATEQUES_SENT, tbl_QUES.QCURR, tbl_QUES.LOGNUMQ, tbl_QUES.QUESID, tbl_QUES.CHANID, tbl_QUES.QANSW "
SQLstring = SQLstring & "FROM tbl_QUES "
SQLstring = SQLstring & "WHERE (((tbl_QUES.DATEQUES_SENT) Is Null) AND ((tbl_QUES.QCURR)=Yes) AND ((tbl_QUES.CHANID)=" & CHANIDnum & ") AND ((tbl_QUES.QANSW)=False)) "
SQLstring = SQLstring & "ORDER BY tbl_QUES.CHANQNUM"
'end input of channel ID

'Create an instance of Excel and add a new blank workbook
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(SQLstring, dbOpenSnapshot)

Set oApp = New Excel.Application
oApp.Visible = False
Set oWB = oApp.Workbooks.Add

'Following deletes excess sheets in workbook to leave only one sheet
If oWB.Sheets.Count > 1 Then
Do While oWB.Sheets.Count > 1
oWB.Sheets(oWB.Sheets.Count).Delete
Loop
End If
'End deletion of extra sheets

'To hide collumn
'oWB.Sheets(1).Collumns("B:B").EntireColumn.Hidden = True ' does not work
'oWB.Sheets(1).Collumns(10).EntireColumn.Hidden = True ' does not work
'oWB.Sheets(1).Collumns(1).Hide ' Does not work
'oWB.Sheets(1).Collumns(1).Visible = False ' Does not work


'StartCell filling with Q at row 8
RowStart = 8
CollumnStart = 1 'offset for data on spreadsheet

'Check record length & set this for formatting purposes
rst.MoveFirst
rst.MoveLast
NumRows = rst.RecordCount
rst.MoveFirst
'end check record length for formatting

'Check record width & set this for formatting purposes
NumCollumns = rst.Fields.Count

'Add the field names as column headers (optional)
For i = 0 To rst.Fields.Count - 1
oWB.Sheets(1).Cells(RowStart, i + 1 + CollumnStart).Value = rst.Fields(i).Name 'Reads name in from Database, 2 is for begin in second collumn (need to change if QUESIDNUM is added)
Next

TempString = RowStart & ":" & RowStart ' make string to define range definition based on RowStart

oWB.Sheets(1).Range(TempString).Font.Bold = True
oWB.Sheets(1).Cells(RowStart + 1, 1 + CollumnStart).CopyFromRecordset rst 'Copies in recordset - offset 1 for header data
oWB.Sheets(1).Name = "QuestionList" 'Set Worksheet Name

Set oSheet = oWB.ActiveSheet
Set oRng = oSheet.Range("B1", "E1") 'range is set from A1-> D1

oSheet.Range("B1").ColumnWidth = 15 'works
oSheet.Range("C1").ColumnWidth = 50 'works
oSheet.Range("D1").ColumnWidth = 50 'works

'Use with selection method - select question area

'Dim SetLineStyle As String
'SetLineStyle = xlContinuous

'Need better way (with numbers) to select ranges within EXCEL from Access
TempString = "B" & RowStart & ":" & "G" & (NumRows + RowStart) ' still to implement collumn width setting of range
'End better range definition

With oWB.Sheets(1).Range(TempString)
.Borders.LineStyle = xlThick
.Borders(xlRight).LineStyle = xlContinuous
.Borders(xlLeft).LineStyle = xlContinuous
.Borders(xlTop).LineStyle = xlContinuous
.Borders(xlBottom).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.VerticalAlignment = xlTop
.WrapText = True
End With

'Set oResizeRange = oSheet.Range(.Cells(1, RowStart), .Cells(3, RowStart + NumRows)) ' Does NOT work
'Set oResizeRange = oSheet.Range(Cell(1, 1), Cell(5, 6)) 'Does NOT work
'Set oResizeRange = oSheet.Range("A8:C30") 'Does work

'Clean up ADO Objects
rst.Close
Set rst = Nothing

'Create a folder if not exist
Dim strFilePath As String
Dim strFolder As String
strFolder = "C:Temp"
strFilePath = strFolder & "Rpt_" & Format(Now(), "yyyymmdd_HHmmss") & ".xls"

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(strFolder) Then
'Create the file
FileSystem.MkDir (strFolder)
End If

'Clean up Excel Objects
oWB.Close SaveChanges:=True, FileName:=strFilePath
Set oWB = Nothing
oApp.Quit
Set oApp = Nothing

'Open the file after export to excel
Shell "EXCEL.EXE """ & strFilePath & "", vbNormalFocus

End Function

hello everyone,

I want to try something new in my database.
Well, I have a working VBA code for export my selected datas from database to excel.
But now I would like to do that again with pdf document.
The source will be the same like for excel, but I want to export everything to a report called "Položky skladu".
It is existing report in my database and it´s source is a query called "D_Položky skladu".
Could somebody help me with this?


VBA code for Excel export:

Private Sub Export_XLS_Click()

Dim MyDB As Database
Dim qdfTemp As QueryDef
Dim xSql As String, xPortFileName As String

xSql = Me.RecordSource

Set MyDB = CurrentDb

On Error GoTo xErr

With MyDB

xPortFileName = "Excel_Položky skladu"
Set qdfTemp = .CreateQueryDef(xPortFileName, xSql)

DoCmd.OutputTo acOutputQuery, qdfTemp.Name, , , -1, , , acExportQualityPrint

.QueryDefs.Delete xPortFileName

Exit_Export_XLS:
Exit Sub
xErr:

If Err.Number = 2501 Then
MsgBox "Export do *.xls zrušený používateľom", vbInformation, "INFO"
Else
MsgBox Error$
.QueryDefs.Delete xPortFileName
End If

End With

Resume Exit_Export_XLS

End Sub

Hello All,

I'm a newbie here. I need to see if someone can provide me with the VBA code to export a table that does a filter by city on a large customer table and produces separate Excel workbooks that have the Customer information for each respective City. I've found several different scripts through google, but none of them seem to work for Access 2007.

Can anyone provide me with a simple script?

Thanks,
Hutch

Hello all,

I have created an access database that I export a query into an Excel
worksheet and do a little formating and what not. Now I would like to do
two things to the workbook. I want to create a toolbar and then add two
buttons, one to sort the list by last name and another to sort it by room number. This is going to be used for down time procedure so I want it to be as easy to use for whom ever is going to be here at the time.

TIA,
Rodger

I have a query I want exported to Excel. I then want to format the Excel file for column width, font, font size, bolding, etc. I created the macro in Access 2007 to export the query and then open in Excel. I then converted the macro to VBA. No problems there. Tested and everything is working. In Excel I recorded a macro doing all the formatting changes I want.

This is where my confusion comes in. I am not sure what needs to be done in Access to automate the formatting from the Access side. I have searched the forum but most of the posts I see are trying to format the exported data and also accomplish something else. I am not sure what code is for the formatting and what code is for the other stuff.

Below is the VBA code to export the query to Excel:

Private Sub butExcelAllUsers_Click()
On Error GoTo butExcelAllUsers_Click_Err
DoCmd.OutputTo acOutputQuery, "qryMyTasksAllUsers-ForExcel", "Excel97-Excel2003Workbook(*.xls)", "", True, "", 0, acExportQualityPrint

butExcelAllUsers_Click_Exit:
Exit Sub
butExcelAllUsers_Click_Err:
MsgBox Error$
Resume butExcelAllUsers_Click_Exit
End Sub


Can you help me with the additional code needed to perform the formatting once the query has been exported? Like I said, I already have the Excel VBA code for all my formatting, I just need the Access code to make it work.

Hi, I am a newbie to both Access and Excel VBA, the following is the Excel VBA code I is used to select 5 rows by double clicking the cell in the first column corresponding to that row. The code is working fine in excel.

But, I need Access VBA code, which opens the excel file and performs the same function.

Can anyone suggest me how to do this? Thanks a million in advance.


	Code:
	 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ColRange As String, PoliciesCount As Integer, LastUsedRow As Long
If Target.Count > 1 Then Exit Sub
If WorksheetFunction.CountA(Cells) > 0 Then
LastUsedRow = Cells.Find(What:="*", After:=[A1], _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row
End If
ColRange = "A2:A" & LastUsedRow
If Intersect(Target, Range(ColRange)) Is Nothing Then Exit Sub
Target.Font.Name = "marlett"
If Target.Value  "a" Then
PoliciesCount = Columns(1).SpecialCells(xlCellTypeConstants, 23).Cells.Count
    If PoliciesCount > 5 Then
        MsgBox "You cannot select more than 5 rows", vbOKOnly Or vbCritical, "Warning!"
        Exit Sub
    End If
    Target.Value = "a"
    Cancel = True
    Exit Sub
End If
If Target.Value = "a" Then
    Target.ClearContents
    Cancel = True
    Exit Sub
End If
End Sub



How to write VBA code to import excel files into a table in Access with a click of a button? The excel file and access table has same column name.

Hi all,

I need to create a vba code to import from an excel spreadsheet 1 specific cell containing some currency data for each of my registered customers.

The excel spreadsheet contain more than 60 columns so I am not willing to upload the whole spreadsheet.

Having in mind that in the excel spreadsheet the column A have the customer ID and column W have the currency I need to upload, my idea was to loop through my existent customers in the database and using its ID number search for it in the excel spreadsheet column A and upload the data in column W when customerID matches.

My question is: how can I, from access, create a loop through the cells in excel to compare with my database information? Or how can I upload only my columns A and W without knowing exactly the number of lines that it contains?

I hope it makes sense,

Thanks for your help

Hi everyone!
I need to find a way to export particular data from a table, to be sent to our other offices in other cities so that they can import it into their database (Which is identical to the one I have)

So that we all have the same data after I have entered it. The transport method will have to be email, as our offices are in diferent cities.

What is the the most easiest, idiot proof, a monkey could do it, kind of way?


At the moment I can copy and paste the data out of the table into excel, send the excel worksheet. They then open the excel sheet, copy and paste the data into their access table. This method is long and prone to human error though! Any suggestions?

Hi Guys

I have a combobox with 10 departments. On the form there are 10 labels as well.My requirement is when I select a dept name from the combobox then it should display that on label1 and for the rest of the labels, all other departments should be displayed.
Now suppose I select another department from the combobox then again on label1 the current selected department name should be displayed and the rest of the labels would display the other 9 departments.

Can anyone please help me with the vba code for this please.I have written the following code in excel/vba but combobox list property is not working in access/vba.

	Code:
	Private Sub Combo8_LostFocus()
Dim lngIndex As Long
    Dim lngControlIndex As Long
    
    lngControlIndex = 18
    For lngIndex = 0 To Combo8.ListCount - 1
        If lngIndex = Combo8.ListIndex Then
            Me.Controls("Label18").Caption = Combo8.List(lngIndex)
        Else
            lngControlIndex = lngControlIndex + 1
            Me.Controls("Label" & lngControlIndex).Caption = Combo8.List(lngIndex)
        End If
    Next
End Sub

Regards
AMan

Hi Access VBA Excel automation gurus,

I have been searched the web the past 2 days to find a access vba function to find a text in a excel file and return the column number, but I can not find it. Please help!

The Access vba function will be allow to be reused by passing parameters. I would like to be able to pass the excel file name, sheet name, and the text string I want to find. It will return the column number.

Your help will be greatly appreciated!

I am having a problem trying to find VBA code to import an excel 2007 file into my access 2007 dao database. My problem is that with each excel record I need to create 2 different records which will be written into different tables in my database.

I have a button on a form in an Access 2007 DB that opens a large Excel sheet with some simple VBA code. I would like to add to the VBA code so it will search the worksheet after it opens for a cell that matches a value passed from a form control (text box), and then goes to that cell in the visible window. Essentially just using the VBA code to execute the Find function. I don't have a lot of experience with VBA and the Excel objects. Any help would be greatly appreciated!

Hi,

Would anyone please be able to help me with some VBA code to repetitively clear the contents of a cell range in an excel worksheet subject to some values in other cells of the same worksheet ?

In cell E2 I have two possible values, "In Play" or "Not In Play" (quotations marks only for illustation, not actually part of value) and in cell F2 I have one possible value, "Suspended".

In column T for cells T5 to T25 I can have 4 possible values, "PLACED", "PENDING", "CANCELLED" or a numerical value.

The worksheet is constantly refreshed from an external data source and I hope to be able to use something like an event property or similar that will react to the sheet refreshing or recalculating to initiate the VBA code.

If the cell contents in range T5 to T25 contain any value other that "PENDING" then I wish to clear the cell contents if E2 = "Not In Play" and F2 "Suspended".

If F2 = "Suspended" or "E2" = "In Play" I do not wish the cell contents in the range T5 to T25 to be cleared.

I think that I need some type of nested IF statement but just do not know where to start. I can open the VBA editor and paste the code into the specific worksheet but I am stuck with the code.


Thx & Rgds
Growlos

Hi,

Have used the access forum but this is my first foray into excel! I am hoping that someone maybe able to assist with the following query:

I have a pivot table that has two columns and 29 rows. The essential function that i'd like the VBA code to perform is to open each row to a new worksheet and the for each (29) worksheets to be copied and transferred into new workbooks (the workbooks would then be saved a name contained in the cell). I have been able to write a code that performs this function but i cannot get it down the pivot table repeating this. Code attached below:
PHP Code:
Sub Macro1()

'
' Macro1 Macro
Dim Cell As Range
Dim b As Integer
Dim a As String
Dim d As String
Dim ws As Worksheet


Range("B4").Select
Selection.ShowDetail = True
ActiveSheet.Name = Range("D2").Text
Cells.Select
Selection.Copy
ActiveSheet.Name = Range("D2").Text
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
ActiveSheet.Name = Range("D2").Text
Cells.Select
    Cells.EntireColumn.AutoFit
    ActiveWindow.Zoom = 85
    Selection.RowHeight = 14.25
    Cells.EntireColumn.AutoFit
Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.Delete
Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.Delete
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
ActiveWorkbook.SaveAs Filename:="J:" & Range("D2")
ActiveWorkbook.Close
End Sub 


How to trough VBA code to create dynamic table in MS Access 2003? Any sample code is welcome. TQ


Not finding an answer? Try a Google search.