Topics Search

Excel Automation - Blank worksheet/file

Excel Automation - Blank worksheet/file
Views: 1
Dim appExcel As Excel.Application
Dim ExcelWbk As Excel.Workbook
Dim ExcelWks As Excel.Worksheet

Dim sTemplate As String ' used for Template file path
Dim sTempFile As String ' used for Template file neame
Dim sOutput As String ' used for Output file name

Dim lRecords As Long ' used for count of number of records
Dim iRow As Integer ' Spreadsheet row index
Dim iCol As Integer ' Spreadsheet column index
Dim iFld As Integer ' Record field number index

Const cTabOne As Byte = 1 ' used to select 'Sheet 1'
Const cTabTwo As Byte = 2 ' used to select 'Sheet2'
Const cStartRow As Byte = 7 'initial Row index
Const cStartColumn As Byte = 1 'initial Column index
Sponsored Links:

More topics

 

Peforming Excel operations via Acces automation

Preview
Access 2007 issue, however could relate to earlier versions.

I have an Excel template that has a series of blank rows

Row1 headings
row 2 headings
rows 3 to 30 blank rows
row 34 formulas to sum values in columns =Sum(B4:B33)

Repeated about 5 times further down the worksheet

Access opens the workbook and uses CopyToRecordset to populate Excel from row 4 downwards. Will never be more than 30 rows.

But what I want to do is when the copy has taken place I want to remove blank rows when a row in column B is blank.

What I did was something like

For X = 4 to 250
If Range("B" & X).Value = "" Then
delete the row
End If Next

But could not get it to work
 

Trying to run a macro in Excel from inside Access

Preview
I've been opening comma delimited files in Access, used an Excel function to re-save those files in .xls which Access readily understands, then opening said files and running a query on them to organize them.

Now the file needs to go to Excel where it is entered into a worksheet with a certain heading.

Problem is when opening the .xls converted file in Excel, no macros show up. If I open a blank worksheet in Excel my macro shows up. I lowered the security settings to the most basic level in Excel.

How do I make the macro show up in the file opened in Excel through the Access VBA, so I can run it?
 

Importing the second excel worksheet

Preview
I'm trying to import the second worksheet in an excel file to access. I am wondering if this is possible
 

objXLApp.Visible = True Issue

Preview
I have a good size project in VBA sending information from 2 different queries to an Excel sheet which maps and saves the file. I want to open the new excel file so my code looks like this at the very end

objXLApp.Visible = True

I want to open the file with the second worksheet showing. So how can I have the file open, but show the second worksheet
 

Importing certain cells from a specific Excel Worksheet and File into a field

Preview
I'm working on a database project as an intern and was asked to create a simple database. I know a little bit of VBA coding (not a WHOLE lot) and I was wondering if their was some pre-existing code that automatically, when I click a button in a form, allows the user to select the worksheet and file that he/she would like to import data from (Excel file that is) andautomatically grab the data from a certain cell range and place them in their appropriate fields in Access.
 

Importing certain cells from a specific Excel Worksheet and File into a field

Preview
I'm working on a database project as an intern and was asked to create a simple database. I know a little bit of VBA coding (not a WHOLE lot) and I was wondering if their was some pre-existing code that automatically, when I click a button in a form, allows the user to select the worksheet and file that he/she would like to import data from (Excel file that is) and automatically grab the data from a certain cell range and place them in their appropriate fields in Access. Where would I put this code into?
 

Import Worksheet From Excel 2010

Preview
You can share data between Access 2010 and Excel 2010 in many ways. Excel worksheet consists of cells that are organized into columns and rows, Access recognize them as fields and records. Access 2010 provides an easy way to import Excel worksheets, this post will explain in detail how to import worksheet in Access from Excel 2010.
 

Importing excel file to append a table

Preview
Is the record is already existing in access, but a few fields are blank, is there any way to import an excel file that just updates those blank fields for the specific record?

For example,

I have an excel file that includes files that are ready to ship out. My access database already has those file names, but does not have the shipping box number. Is there any way to importthe excel file into my database, have it find those specific file names and update the shipping box number field? The excel file won't be adding any new records,
 

Macro to paste data from Access Tables to Ms Excel

Preview
Please find attached sample MS Acces db and Ms excel file.

I need the data from the 3 Access tables to be populated across to the three tables as shown in the MS Excel file.

Assume the file path of the excel file is: C:\Documents and Settings\All Users\Documents\microsoft\Sample_File.xls

so the table Sample_Data_1 gets populated across to MS Excel worksheet Records_1 C11:F14 and so on.

In the original file I have a sub form which runs the macro.
 

Import, export, and link data between Access and Excel

Preview
There are several ways to exchange data between Microsoft Office Access and Microsoft Office Excel.
To bring data into Excel from Access, you can copy data from an Access datasheet and paste it into an Excel worksheet, connect to an Access database from an Excel worksheet, or export Access data into an Excel worksheet.