How do I export only visible cells from Excel to CSV?

Question

I am trying to sort out some VBA in Access today with very limited knowledge. I have constructed the code below for a simple insert into a table within the same access database.

I am getting the error "Missing semicolon (;) at end of SQL statement."

However I have the semicolon in there. I can only assume I have made a rookie error with syntax somewhere but after spending far too long trying I am stumped, any pointers much appreciated!

Dim StrSQL As String
Dim InDate As Date
Dim VacNum As String
Dim Stage As String

InDate = Now()
VacNum = Me.Vacancy_No
Stage = Me.Current_Stage

StrSQL = "INSERT INTO Stage (Vacancy_ID,StageDate,Stage) VALUES ('" & VacNum & "','" & InDate & "','" & Stage & "' );"

DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True

Solution 1

The question seems to be "How do I copy only visible cells?"

The answer is:

1. Select only the visible cells by first selecting the range then using GoTo (Alt+G), Special, Visible Cells only [This is the same as Sean Cheshire's answer of using Alt-; to select the visible cells only but it shows the path more explicitly].
2. Ctrl+C (to copy)
3. Go to a second sheet and paste them (Ctrl+V)

Solution 2

Temporary removal seems a little risky in view of possible interruptions (eg to power!) so I’d suggest working on a copy would be safer. How to copy visible cells only is explained here. However if the hidden rows are all at the top, you could select the first ‘not hidden’ row down to the last and just copy that area. Then export from copy.

Solution 3

My best idea would be to remove the cells temporarily and then revert back to a previous state of the file.

To remove the cells:

1. Go to File, Check for Issues, Inspect Document.
2. Check only Hidden Rows and Columns, then click Inspect.
3. Click Remove All on the Hidden Rows and Columns line.
4. Click Close.

Comments

[SeanC]

a shortcut key to selecting visible cells only is ALT+; - highlight your range, press ALT-:, CTRL-C and then CTRL-V to another sheet/workbook

[SeanC]

as I can't use kbd tags in comments, it may be a little clearer if I say its ALT and semi-colon to change a selection to only be visible cells

[David]

Sadly this doesn't seem to work if you filter out rows between your normal range (if rows aren't hidden). Excel complained that data too complex to go forward with the copy.

[LCousins]

Thankyou, that worked well, I will find some time later today to look into and understand how its working, i would always rather do things the proper way, now i know what to look up. Many thanks

[Erik A]

You can review this answer, it might provide you more insight. Also, one of the many sources on SQL injection might provide some insight.

[RolfBly]

In Excel/Office 2016, the shortcut to select only visible cells is Ctrl + g, not Alt + g. Also, this selects visible empty columns too, so you'll want to hide those first. Yeah I'm late to the party, I know.