Ms-access vba - read from excel and also update that excel

0 votes

I made a straightforward Access database with just 1 form and 1 button to execute code that starts a blank Excel document (with 1 worksheet) and enters "X" in the first cell. Although the workbook is hidden and needs to be manually unhidden, it performs the job. That is, when I open the excel file after the VBA code has run, everything is greyed out. The cell was updated as necessary, and all is OK if I click the "view" tab and choose the "Unhide" option. The workbook is not hidden if I remove the VBA line that writes "X" in the excel file. How can I fix the workbook being hidden issue?

Here is the code:

Private Sub Command0_Click()
    Dim my_xl_app As Object
    Dim my_xl_worksheet As Object
    Dim my_xl_workbook As Object
    Set my_xl_app = CreateObject("Excel.Application")
    my_xl_app.UserControl = True
    my_xl_app.Visible = False    ' yes. I know it's the default
    Set my_xl_workbook = GetObject("D:\Dropbox\MASAV\HIYUVIM\AAA.xlsx")
    Set my_xl_worksheet = my_xl_workbook.Worksheets(1)
    my_xl_worksheet.Cells(1, "A") = "V"
    my_xl_workbook.Close SaveChanges:=True
    Set my_xl_app = Nothing
    Set my_xl_workbook = Nothing
    Set my_xl_worksheet = Nothing
End Sub
Dec 28, 2022 in Others by Kithuzzz
• 38,010 points
1,163 views

1 answer to this question.

0 votes

Here is the code that works without hiding my entire workbook :

Private Sub Command0_Click()
    Dim my_xl_app As Object
    Dim my_xl_worksheet As Object
    Dim my_xl_workbook As Object
    Set my_xl_app = CreateObject("Excel.Application")
    Set my_xl_workbook = my_xl_app.Workbooks.Open("D:\Dropbox\MASAV\HIYUVIM\AAA.xlsx")
    Set my_xl_worksheet = my_xl_workbook.Worksheets(1)
    my_xl_workbook.Sheets(1).Range("A1").Value = "V"
    my_xl_workbook.Close SaveChanges:=True
    Set my_xl_app = Noth
answered Dec 28, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Excel VBA: Trying to read all files in folder based on cell input and output to another cell

Your array has no capacity for data ...READ MORE

answered Jan 24, 2023 in Others by narikkadan
• 63,420 points
407 views
0 votes
1 answer

Excel VBA - Need to get a specific line from TextBox and send to an specific Cell

Moving your string into an array first ...READ MORE

answered Feb 2, 2023 in Others by narikkadan
• 63,420 points
485 views
0 votes
1 answer

Looping through a folder with Excel files and scraping date from selected cells usin VBA

You record the outcome of your comparison ...READ MORE

answered Feb 16, 2023 in Others by narikkadan
• 63,420 points
655 views
0 votes
1 answer

How can I find and replace text in Word using Excel VBA?

Try this code Option Explicit Const wdReplaceAll = 2 Sub ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 63,420 points
3,781 views
0 votes
1 answer

Multiple find and replace in MS Word from a list in MS Excel

If I understand you correctly, you want ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,420 points
387 views
0 votes
1 answer

Trying to create an enclosing bookmark after pasting a picture from Excel into Word using VBA

Try this: Sub IMPORT_TO_WORD() Dim ws1 As Worksheet, ws2 ...READ MORE

answered Oct 30, 2022 in Others by narikkadan
• 63,420 points
586 views
0 votes
1 answer

Indent table pasted from Excel into Word

It is feasible to pick up the ...READ MORE

answered Oct 30, 2022 in Others by narikkadan
• 63,420 points
352 views
0 votes
1 answer

Uipath(RPA) : read data from the PDF file and write to Excel file

If you want to use UiPath and ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 63,420 points
1,693 views
0 votes
1 answer

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: $file_name = htmlentities($_POST['file_name']); $sheet_name = htmlentities($_POST['sheet_name']); $number_of_columns = htmlentities($_POST['number_of_columns']); $number_of_rows ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 63,420 points
6,772 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP